Re: [GENERAL] Row-Level Access Control via FK to pg_catalog.pg_authid

[Tom Lane]

David Fetter <[EMAIL PROTECTED]> writes:
> I'm working on a way to do row-level access via VIEWs and ROLEs.

You sure you're not re-inventing the wheel?
http://pgfoundry.org/projects/veil/

regards, tom lane

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

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

Re: [GENERAL] Odd behaviour of timestamptz

[Tom Lane]

Scott Marlowe <[EMAIL PROTECTED]> writes:
> On Wed, 2007-02-21 at 13:21, hubert depesz lubaczewski wrote:
>> On 2/21/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>>> I'll bet you are running in Europe/Amsterdam time zone?  The
>>> above is
>> 
>> what about me? i'm in poland, and runing in europe/warsaw time zone. i
>> assume we also had some issues lie this - where can i read about it? 

> Take a look here:

> http://en.wikipedia.org/wiki/Time_zone

Also, the zic data that Postgres uses can be seen in our CVS:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/data/

The comments in those files are, um, extensive.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Joshua D. Drake]

John Smith wrote:
> On 2/21/07, Lincoln Yeoh  wrote:
>> MySQL: the PHP of databases.
> 
> 'd appreciate if you stick to the subject.

Oops he probably should not have used MySQL because it is trademarked...

mysql: The PHP of databases

;)

Sincerely,

Joshua D. Drake


> jzs
> 
> ---(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
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

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

Re: [GENERAL] Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)

[Tom Lane]

"Ian Harding" <[EMAIL PROTECTED]> writes:
> I had views that used syntax like
> WHERE datecol < current_date and (otherdatecol is null or otherdatecol
> > current_date)
> Suddenly, this is ungodly inefficient in 8.2.3.  It worked just fine in 8.1.3.

This complaint is pretty much content-free (especially with the oblique
implication that it's got something to do with left joins).  Please
provide a self-contained test case.

regards, tom lane

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

   http://archives.postgresql.org/

[GENERAL] what compression is used in on disk bitmap index implementation

[sangeetha k.s]

i want to know
 1.what compression technique used in on disk implementation of bitmap
index.
 2.if we want add a new thing to the development how we can test that
with the database

Re: [GENERAL] postgresql vs mysql

[brian]

Chris wrote:


It's not an oddity.

An empty string is a KNOWN value. You know exactly what that value is - 
it's an empty string.


A NULL is UNKNOWN - it doesn't have a value at all.



Just to expand on that (and to drag this thread out a little longer), i 
find that a pretty good way to get across this difference is to refer to 
it as *the* empty string. I prefer this description because it is 
implicit that it is still a string. NULL has no type, regardless of the 
column it is inserted into. Thus, the empty string is NOT NULL.


brian

---(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 vs mysql

[John Smith]

On 2/21/07, Lincoln Yeoh  wrote:

MySQL: the PHP of databases.


'd appreciate if you stick to the subject.
jzs

---(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] postgresql vs mysql

[Chris]

CaT wrote:

On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote:

In postgres, to stop an empty blank string:

create table a(a text not null check (char_length(a) > 0));


What's wrrong with using

a <> ''

sd the check? Or is this just a flavour thing?


Nothing, I just thought of the other way first :)

Probably better doing it as a <> '' otherwise postgres might have to run 
the char_length function every time you do an insert (ie might be a very 
slight performance issue).


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] Row-Level Access Control via FK to pg_catalog.pg_authid

[Alvaro Herrera]

David Fetter wrote:
> Folks,
> 
> I'm working on a way to do row-level access via VIEWs and ROLEs.  The
> idea:
> 
> Given a table foo with pk foo_id, which is to be the subject of these
> row-level permissions, I'd make another table, say can_read_foo, which
> looks like:
> 
> CREATE TABLE can_read_foo (
> foo_id INTEGER NOT NULL REFERENCES foo(foo_id),
> rolname NAME NOT NULL REFERENCES pg_catalog.pg_authid(rolname) /* OOPS! */
> );
> 
> Then a VIEW my_foo that uses CURRENT ROLE and JOINs foo, can_read_foo,
> and some clever recursive role spidering in order to determine what
> rows to present to a particular role on SELECT.
> 
> The problem is that that foreign key to pg_catalog.pg_authid is
> generically disallowed.  This is because (thanks for explaining,
> Andrew of Supernews) it's a shared catalog, so other DBs must be able
> to modify it without looking inside the one I have this installed in.
> Other than MySQLishly leaving an unenforced FK constraint to pg_authid
> flapping in the breeze, is there any way to handle this?

Maybe you can install a pg_shdepend entry instead of using a real FK?
The problem then is that if you do DROP ROLE CASCADE, your "foo" object
will go away ... or rather, an elog(ERROR) will be raised saying that
the "foo" object class is unknown.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] postgresql vs mysql

[Richard Broersma Jr]

> Does PostgreSQL suffer from this oddity as well? This distinction
> between an empty string and a NULL? Could you also please give me an
> example of where this would be useful from a business logic
> standpoint? Why should a NULL be different from an empty string,
> what's the big mysterious difference?


Nulls are also useful for data that is imported where there is not always a way 
to determine
certain field values for a record.

Now suppose in a query you want to return all record that meet your criteria as 
well as any that
might meet your criteria depending if the null value was actually known.

Select * from table
Where ((field1,field2,field3) = ('farmer','baker','shoemaker')) is unknown;

Now you can get all records that may meet your criteria.

Regards,
Richard Broersma Jr.

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

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

Re: [GENERAL] How can you tell if a function is immutable from psql?

[Michael Fuhr]

On Wed, Feb 21, 2007 at 09:20:19AM -0600, Michael Nolan wrote:
> Am I just missing it or is there no way to tell if a function is noted as
> immutable from the \df or \df+ output in psql?

Apparently not.  You could examine pg_proc.provolatile.

http://www.postgresql.org/docs/8.2/interactive/catalog-pg-proc.html

-- 
Michael Fuhr

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

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

Re: [GENERAL] postgresql vs mysql

[Chris]

Erick Papadakis wrote:

So how should I make a database rule in MySQL to not allow blank
strings. Basically to REQUIRE a value for that column, whether it is
NULL or NADA or VOID or whatever you wish to call it. I just want to
make sure that something, some value, is entered for a column. Would
appreciate any thoughts or pointers.

Does PostgreSQL suffer from this oddity as well? This distinction
between an empty string and a NULL? Could you also please give me an
example of where this would be useful from a business logic
standpoint? Why should a NULL be different from an empty string,
what's the big mysterious difference?


It's not an oddity.

An empty string is a KNOWN value. You know exactly what that value is - 
it's an empty string.


A NULL is UNKNOWN - it doesn't have a value at all.



In postgres, to stop an empty blank string:

create table a(a text not null check (char_length(a) > 0));

though that allows a single space in..

See http://www.postgresql.org/docs/8.2/interactive/ddl-constraints.html

and

http://www.postgresql.org/docs/8.2/interactive/ddl-alter.html#AEN2302


No idea about how to do this in mysql, search their documentation.

--
Postgresql & php tutorials
http://www.designmagick.com/

---(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] postgresql vs mysql

[Rich Shepard]

On Thu, 22 Feb 2007, Erick Papadakis wrote:


Why should a NULL be different from an empty string, what's the big
mysterious difference?


  Long ago and far away, when I was in the Army, we had quite a few soldiers
whose name took the form 'John NMI Doe.' That's because the Army -- even
before computers were ubiquitous -- needed something in the forms for middle
initials. If you did not have a middle name or initial, one was assigned to
you: NMI (No Middle Initial). This did not mean that the middle initial was
unknown (NULL), but that it did not exist (blank).

  If you have a credit card, would you prefer the balance to be blank or
unknown? How would the bank select all those with no balance rather than
those for whom a balance is unknown?

  I'm sure you can think of dozens of more situations like these.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [GENERAL] postgresql vs mysql

[Rodrigo Gonzalez]

Erick Papadakis wrote:

So how should I make a database rule in MySQL to not allow blank
strings. Basically to REQUIRE a value for that column, whether it is
NULL or NADA or VOID or whatever you wish to call it. I just want to
make sure that something, some value, is entered for a column. Would
appreciate any thoughts or pointers.

Does PostgreSQL suffer from this oddity as well? This distinction
between an empty string and a NULL? Could you also please give me an
example of where this would be useful from a business logic
standpoint? Why should a NULL be different from an empty string,
what's the big mysterious difference?


Just an example:

middle name = '' mean no middle name
middle name = NULL mean "dB does not know if there is or not middle name"

I hope you understand the difference between empty and null. and for 
numbers is other thingin average for example null is not considered, 
 other values, yes




Thanks.



On 2/22/07, Ron Johnson <[EMAIL PROTECTED]> wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 18:09, Erick Papadakis wrote:
> How would you like to use a database that has nuances like these --
> http://forums.mysql.com/read.php?20,141120,141120#msg-141120

Huh?

A blank string (does that mean '' or ' '?) is not NULL, so of
*course* it should pass the NOT NULL constraint.

Or am I missing something?


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

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





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

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

Re: [GENERAL] postgresql vs mysql

[Erick Papadakis]

So how should I make a database rule in MySQL to not allow blank
strings. Basically to REQUIRE a value for that column, whether it is
NULL or NADA or VOID or whatever you wish to call it. I just want to
make sure that something, some value, is entered for a column. Would
appreciate any thoughts or pointers.

Does PostgreSQL suffer from this oddity as well? This distinction
between an empty string and a NULL? Could you also please give me an
example of where this would be useful from a business logic
standpoint? Why should a NULL be different from an empty string,
what's the big mysterious difference?

Thanks.



On 2/22/07, Ron Johnson <[EMAIL PROTECTED]> wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 18:09, Erick Papadakis wrote:
> How would you like to use a database that has nuances like these --
> http://forums.mysql.com/read.php?20,141120,141120#msg-141120

Huh?

A blank string (does that mean '' or ' '?) is not NULL, so of
*course* it should pass the NOT NULL constraint.

Or am I missing something?


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

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

Re: [GENERAL] postgresql vs mysql

[Michael Fuhr]

On Wed, Feb 21, 2007 at 01:45:08PM -0600, Ron Johnson wrote:
> On 02/21/07 08:42, Michael Fuhr wrote:
> > Not as good as "ERROR: hey bonehead, there ain't no such date" but
> 
> But it *inserts the "data"*!

I didn't say otherwise and I'm not defending MySQL's behavior.  I was
simply refuting the statement that "it doesn't warn you that it didn't
like the input format."

> > at least it's something :-)
> 
> Sure, at the interactive command line.
> 
> What kind of error code does this return to applications?  Can a PHP
> or C programmer catch this warning, or does MySQL return a success code?

Beats me; I care about my data so I don't use MySQL.  Since it's a
warning I expect the query returns success.  The C API has a
mysql_warning_count() function that appears to be exposed in PHP's
mysqli extension as mysqli_warning_count.  That C function doesn't
appear in the source code for any of the other MySQL extensions in
PHP 5.2.1.

-- 
Michael Fuhr

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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Chris]

Erick Papadakis wrote:

How would you like to use a database that has nuances like these --
http://forums.mysql.com/read.php?20,141120,141120#msg-141120


Err - an empty string is not the same as null, so that is perfectly valid.

Null means unknown, an empty string is not unknown - it's a known value 
(which happens to be nothing, but it's still known).


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] postgresql vs mysql

[Brusser, Michael]

 
>> How would you like to use a database that has nuances like these -- 
>> http://forums.mysql.com/read.php?20,141120,141120#msg-141120

---

> Huh?
> A blank string (does that mean '' or ' '?) is not NULL, so of
> *course* it should pass the NOT NULL constraint.
> Or am I missing something?

---

I agree with you, although Oracle won't, they really blurred the line
between the empty string and NULL.

As for MySQL maybe this article makes more sense:
http://www.databasejournal.com/features/mysql/article.php/3519116

Mike.



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

Re: [GENERAL] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 19:01, Brusser, Michael wrote:
>  
>>> How would you like to use a database that has nuances like these -- 
>>> http://forums.mysql.com/read.php?20,141120,141120#msg-141120
> 
> ---
> 
>> Huh?
>> A blank string (does that mean '' or ' '?) is not NULL, so of
>> *course* it should pass the NOT NULL constraint.
>> Or am I missing something?
> 
> ---
> 
> I agree with you, although Oracle won't, they really blurred the line
> between the empty string and NULL.

Well that bites.  We don't use NULLs a lot, but still: NULL is null,
not a blank string.

How do "they" represent NULL in a numeric field?

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3O8cS9HxQb37XmcRAgk3AJ9uB0Z0X9tUOdSgeBggC1UivekXhACgsaLr
ZVYsQ5NaFCuCwgER6mboH70=
=2arU
-END PGP SIGNATURE-

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

Re: [GENERAL] postgresql vs mysql

[Chris]

Adam Rich wrote:
It's got a query parser that's dumb as a brick. 


While we're on this topic...  I have a question on these series
of queries:

-- Query A
select count(*) from customers c
where not exists ( select 1 from orders o
where o.customer_id = c.customer_id )

-- Query B
select count(*) from customers c
where customer_id not in ( select customer_id from orders)


I had a similar sort of query and got told that work_mem is the most 
important thing here.


See http://archives.postgresql.org/pgsql-general/2007-02/msg00986.php

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] postgresql vs mysql

[Chris]

Chad Wagner wrote:
On 2/20/07, *gustavo halperin* <[EMAIL PROTECTED] 
> wrote:


I  have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?


How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an error.  
Totally annoying, I don't know if it was ever fixed.  It seems that 
MySQL's parser is generally weak at syntax validation in it's default 
configuration.


That one actually comes in handy ;) Especially in older versions (4.0) 
that don't support subselects..


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 18:09, Erick Papadakis wrote:
> How would you like to use a database that has nuances like these --
> http://forums.mysql.com/read.php?20,141120,141120#msg-141120

Huh?

A blank string (does that mean '' or ' '?) is not NULL, so of
*course* it should pass the NOT NULL constraint.

Or am I missing something?

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3OFHS9HxQb37XmcRAsdwAJ9Ew3pb2huydeP14Bn8NsWuWn1TnACgw+Ru
qD0UuPJcJukugpER51HMXDs=
=DHkn
-END PGP SIGNATURE-

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

Re: [GENERAL] postgresql vs mysql

[Erick Papadakis]

How would you like to use a database that has nuances like these --
http://forums.mysql.com/read.php?20,141120,141120#msg-141120

ep

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

[GENERAL] Row-Level Access Control via FK to pg_catalog.pg_authid

[David Fetter]

Folks,

I'm working on a way to do row-level access via VIEWs and ROLEs.  The
idea:

Given a table foo with pk foo_id, which is to be the subject of these
row-level permissions, I'd make another table, say can_read_foo, which
looks like:

CREATE TABLE can_read_foo (
foo_id INTEGER NOT NULL REFERENCES foo(foo_id),
rolname NAME NOT NULL REFERENCES pg_catalog.pg_authid(rolname) /* OOPS! */
);

Then a VIEW my_foo that uses CURRENT ROLE and JOINs foo, can_read_foo,
and some clever recursive role spidering in order to determine what
rows to present to a particular role on SELECT.

The problem is that that foreign key to pg_catalog.pg_authid is
generically disallowed.  This is because (thanks for explaining,
Andrew of Supernews) it's a shared catalog, so other DBs must be able
to modify it without looking inside the one I have this installed in.
Other than MySQLishly leaving an unenforced FK constraint to pg_authid
flapping in the breeze, is there any way to handle this?

Thanks in advance for any hints, tips or pointers :)

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

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

   http://archives.postgresql.org/

[GENERAL] (no subject)

[Filipe Fernandes]

---(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] Quering 4 or more physicale different PostgreSQL server at once

[Ben]

You could look at contrib/dblink but if you plan to join between your four 
servers a lot it seems like you might want cleaner solution, like, say, 
slony-I or pgpool, and maybe some data partitioning on that main table as 
well.


On Wed, 21 Feb 2007, Michelle Konzack wrote:


Hello,

My current PostgreSQL Database has arround 560 GByte without indices.
If not more then 3-5 user query it it works fast like Google...  :-)

But I have over 600 Users now which all query it over the Net and hit
a heavy performance problem.

The biggest problem is the "main" table which is arround 80% of the DB.

Now I have made a statistic and know, which Datas are most quried and
like to split the Database (exactly THE MONSTER-TABLE) over at least 4
Physicaly PostgreSQL servers.

The Database is generaly STATIC an Data will only be added and NEVER
removed or changed.  (If DATE or a ROW change, it will get a FULL NEW
ROW with version number)

Since I access the PostgreSQL from a pysicaly seperated dedicated
apache server using php5 I like to know from some Experts how to
query 4 or more Databases ar once and join the data.

I thinking, putting the main database with most requested data on the
first Server which will have arround 180 GByte and then the rest of
"THE" table on the other servers.

Note:  "THE" table is a historical timeline...
  and the datasets are not ordered in the table.

Greetings
   Michelle Konzack
   Systemadministrator
   Tamay Dogan Network
   Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
  50, rue de Soultz MSM LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)



---(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] postgresql vs mysql

[Brandon Aiken]

IMX, the only things going for MySQL are:
1. It's fast.
2. It's easy to install and administer.
3. It's cheap and cross-platform.
4. It's popular.

The problem is:
1. It's fast because fsync is off by default, and MyISAM is not
transactional and doesn't support basic features like foreign keys.
That basically means it's fast because it ignores Boyd and Cobb.  Guess
what?  The same can be said of flat files.
2. Most other RDBMSs have seen the advantage and done this now, too.
Installing an RDBMS is no longer more difficult than installing the rest
of the system.
3. MySQL is no longer the only thing available.  PostgreSQL is on
Windows now, MS SQL 2005 Express, SQLite, Oracle Express, Firebird, etc.
4. So is Windows.

MySQL isn't quite as bad as PHP for internal inconsistencies and
developer aggrivations, but it comes close enough for me to want to
avoid them both.

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of gustavo
halperin
Sent: Tuesday, February 20, 2007 4:26 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] postgresql vs mysql

  Hello

 I  have a friend that ask me why postgresql is better than mysql.
 I personally prefer posgresql, but she need to give in her work 3 or 4 
strong reasons for that. I mean not to much technical reasons. Can you 
give help me please ?

  Thank you,
   Gustavo


-- 
   ||\ // \
   | \\   //   |  
I'm thinking.   \  \\  l\\l_ //|
_  _ |  \\/ `/  `.||
  /~\\   \//~\   | Y |   |   ||  Y |
  |  \\   \  //  |   |  \|   |   |\ /  |
  [   ||||   ]   \   |  o|o  | >  /
 ] Y  ||||  Y [   \___\_--_ /_/__/
 |  \_|l,--.l|_/  |   /.-\() /--.\
 |   >'  `<   |   `--(__)'
 \  (/~`----'~\)  /   U// U / \
  `-_>-__-<_-'/ \  / /|
  /(_#(__)#_)\   ( .) / / ]
  \___/__\___/`.`' /   [
   /__`--'__\  |`-'|
/\(__,>-~~ __) |   |__
 /\//\\(  `--~~ ) _l   |--:.
 '\/  <^\  /^>   |  `   (  <   \\
  _\ >-__-< /_ ,-\  ,-~~->. \   `:.___,/
 (___\/___)   (/()`---'


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



** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.

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

Re: [GENERAL] massive memory allocation until machine crashes

[Martijn van Oosterhout]

On Wed, Feb 21, 2007 at 08:35:40PM +0100, Alexander Elgert wrote:
> Yes, there are five FOREIGN keys in this table:



There's your problem. You've got a trigger set to run after every
delete, and you've got them set to wait until the end of the
transaction. So postgres has to delete all the tuples while
maintaining a list of the deleted tuples so that at the end it can run
the trigger a few million times.

Possibly something like (not sure about this):

SET ALL CONSTRAINTS IMMEDIATE;

or some such may avoid the memory usage and run the trigger straight
away.

> Foreign-key constraints:
>   "visit_cont_mech" FOREIGN KEY (contact_mech_id) REFERENCES 
> contact_mech(contact_mech_id) DEFERRABLE INITIALLY DEFERRED
>   "visit_party" FOREIGN KEY (party_id) REFERENCES party(party_id) 
> DEFERRABLE INITIALLY DEFERRED
>   "visit_role_type" FOREIGN KEY (role_type_id) REFERENCES 
> role_type(role_type_id) DEFERRABLE INITIALLY DEFERRED
>   "visit_user_agnt" FOREIGN KEY (user_agent_id) REFERENCES 
> user_agent(user_agent_id) DEFERRABLE INITIALLY DEFERRED
>   "visit_party_role" FOREIGN KEY (party_id, role_type_id) REFERENCES 
> party_role(party_id, role_type_id) DEFERRABLE INITIALLY DEFERRED

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/07 08:42, Michael Fuhr wrote:
> On Wed, Feb 21, 2007 at 08:54:30AM -0500, Jan de Visser wrote:
>> It gets better: The problem is not just feb 35, it's also that it doesn't 
>> warn 
>> you that it didn't like the input format:
> 
> Actually it did, sort of.
> 
>> mysql> insert into test values ('35-Feb-2007');
>> Query OK, 1 row affected, 1 warning (0.07 sec)
> ^
> mysql> show warnings;
> +-+--+-+
> | Level   | Code | Message |
> +-+--+-+
> | Warning | 1265 | Data truncated for column 'td' at row 1 | 
> +-+--+-+
> 1 row in set (0.00 sec)
> 
> Not as good as "ERROR: hey bonehead, there ain't no such date" but

But it *inserts the "data"*!

> at least it's something :-)

Sure, at the interactive command line.

What kind of error code does this return to applications?  Can a PHP
or C programmer catch this warning, or does MySQL return a success code?
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF3KFES9HxQb37XmcRAlyNAKCiEIAbywwa3jL0q1jlnx+9AfZVIwCg4dOu
cdgyFYs1ECl9Jh7JJ7XLZ9Y=
=ioTM
-END PGP SIGNATURE-

---(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] massive memory allocation until machine crashes

[Alexander Elgert]

Hello.

Richard Huxton schrieb:

Alexander Elgert wrote:

Hello,

given is a postgres database in version

PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2


Upgrade to 7.4.16 as soon as is convenient - you're missing 8 sets of 
bug-fixes.
At the Weekend I turned to 7.4.16, there was no problem, but it does not 
help much...

I ran the command:

delete from visit where date(created_stamp) < date(current_timestamp - 
'8 days'::interval);


but at 1.5GB top:SIZE I aborted the query.

So I divided the set of tuples to be deleted into commands to delete all 
subsets and it works:


delete from visit where date(created_stamp) < date(current_timestamp - 
'360 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp - 
'300 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp - 
'240 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp - 
'180 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp - 
'120 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp - 
'60 days'::interval);
delete from visit where date(created_stamp) < date(current_timestamp - 
'8 days'::interval);


This queries deleted up to 7 Millions tuples and took up to 1GB of RAM.




and there is a table "visit" with 26 million tuples using 8 GB of space



The table is from ofbiz and for logging accesses to the webapplication.
Running a delete command which deletes all but a few tuples causes 
the postmaster to allocate memory:
---10903 postgres  25   0  214M 213M 10412 R95.3 10.5   6:07 
postmaster


Until all memory and swap is gone - that was 1.4GB of top:SIZE


Do you have any triggers or foreign keys on this table? If so, each of 
those will need to be tracked. There may be a memory-leak in 7.4.8 
that's since been fixed, probably worth checking the release notes at 
the end of the manual.



Yes, there are five FOREIGN keys in this table:

ofbiz=> \d visit
   Table "public.visit"
  Column |   Type   | Modifiers
---+--+---
visit_id  | character varying(20)| not null
contact_mech_id   | character varying(20)|
user_login_id | character varying(255)   |
party_id  | character varying(20)|
role_type_id  | character varying(20)|
user_created  | character(1) |
session_id| character varying(255)   |
server_ip_address | character varying(20)|
server_host_name  | character varying(255)   |
webapp_name   | character varying(60)|
initial_locale| character varying(60)|
initial_request   | character varying(255)   |
initial_referrer  | character varying(255)   |
initial_user_agent| character varying(255)   |
user_agent_id | character varying(20)|
client_ip_address | character varying(20)|
client_host_name  | character varying(255)   |
client_user   | character varying(60)|
cookie| character varying(60)|
from_date | timestamp with time zone |
thru_date | timestamp with time zone |
last_updated_stamp| timestamp with time zone |
last_updated_tx_stamp | timestamp with time zone |
created_stamp | timestamp with time zone |
created_tx_stamp  | timestamp with time zone |
Indexes:
  "pk_visit" primary key, btree (visit_id)
  "visit_cont_mech" btree (contact_mech_id)
  "visit_party" btree (party_id)
  "visit_party_role" btree (party_id, role_type_id)
  "visit_role_type" btree (role_type_id)
  "visit_thru_idx" btree (thru_date)
  "visit_txcrts" btree (created_tx_stamp)
  "visit_txstmp" btree (last_updated_tx_stamp)
  "visit_user_agnt" btree (user_agent_id)
Foreign-key constraints:
  "visit_cont_mech" FOREIGN KEY (contact_mech_id) REFERENCES 
contact_mech(contact_mech_id) DEFERRABLE INITIALLY DEFERRED
  "visit_party" FOREIGN KEY (party_id) REFERENCES party(party_id) 
DEFERRABLE INITIALLY DEFERRED
  "visit_role_type" FOREIGN KEY (role_type_id) REFERENCES 
role_type(role_type_id) DEFERRABLE INITIALLY DEFERRED
  "visit_user_agnt" FOREIGN KEY (user_agent_id) REFERENCES 
user_agent(user_agent_id) DEFERRABLE INITIALLY DEFERRED
  "visit_party_role" FOREIGN KEY (party_id, role_type_id) REFERENCES 
party_role(party_id, role_type_id) DEFERRABLE INITIALLY DEFERRED


Greetings,
  Alexander



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

Re: [GENERAL] Odd behaviour of timestamptz

[Scott Marlowe]

On Wed, 2007-02-21 at 13:21, hubert depesz lubaczewski wrote:
> On 2/21/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> I'll bet you are running in Europe/Amsterdam time zone?  The
> above is
> 
> what about me? i'm in poland, and runing in europe/warsaw time zone. i
> assume we also had some issues lie this - where can i read about it? 

Take a look here:

http://en.wikipedia.org/wiki/Time_zone
http://en.wikipedia.org/wiki/UTC+0:20
http://en.wikipedia.org/wiki/List_of_time_zones

---(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

[GENERAL] change data type int4 to serial

[Seb]

Hi,

I am experimenting a migration of an M$ Access database to postgresql in a
Debian GNU/Linux system.  Based on some reading, I've approached it as
follows:

1. Using mdbtools, export the tables from Access to *.csv files,
   sanitizing file names and column names.

2. Using unixodbc and GNU R's package RODBC, import the tables to an empty
   database in postgresql; i.e. create a database in postgresql, read the
   tables into R for cleaning up mbdbtools' job and let RODBC import the
   tables into postgresql, specifying the data type for
   date/time/timestamp columns.

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.


Cheers,

-- 
Seb


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

   http://archives.postgresql.org/

Re: [GENERAL] Odd behaviour of timestamptz

[hubert depesz lubaczewski]

On 2/21/07, Tom Lane <[EMAIL PROTECTED]> wrote:


I'll bet you are running in Europe/Amsterdam time zone?  The above is



what about me? i'm in poland, and runing in europe/warsaw time zone. i
assume we also had some issues lie this - where can i read about it?

best regards,

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

[GENERAL] Sockets in perl (Db transaction ends abnormally at client when server closes client socket)

[Jasbinder Singh Bali]

Hi,
I have a table on which there is a trigger fired after insert.
This trigger opens a socket with another server.
Server, after executing what it has to, closes the client socket
and at the client end (DB side) the whole transaction rolls back saying that
server closed the connection abnormally
which means that the transaction at the DB side ended abnormally and hence
the rollback.

To fix this, i tried closing the client socket at DB end (client side)
itself. Unfortunatel, it keeps running the insert query forever without
accomplishing anything. However, it opens the connection with the server but
doesn't complete its own transaction at all.

Can anyone please help me solving this problem

Thanks,
Jas

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

[Michelle Konzack]

Am 2007-01-30 15:40:23, schrieb Mark Walker:
> Maybe that's just my experience with my customers.  I have seen signs of 
> dysfunctional computer systems lately.  I was in a fast food restaurant 
> in San Francisco a few months back and they were manually taking 
> orders.  I think the only reason they stayed open was because the owner 
> was there.  Last summer a McDonald's in Paris next to the hotel my 
> family was staying at shut down because their computer system was down.  
> It ticked me off because we ended up eating at some pricey cafe next 
> door.  I guess I'm a typical dumb American, traveling all the way to 
> Paris to eat at McDonald's.

:-)

Do you know "Döner Kebab"?

Realy good beek or chicken with fresh salad!
Not this "american" fast-feed.  :-)

And its cheaper!

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
   50, rue de Soultz MSM LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: [GENERAL] PG Email Client

[Michelle Konzack]

Am 2007-01-30 07:56:32, schrieb Sim Zacks:
> In the traditional imap server the mail is stored in folders on the server 
> that are accessed by the client. Therefore you are limited to one indexed 
> location per email. Lets say you want to look at your email by customer. 
> You would want to have a folder per customer so you can review your 
> correspondence with each one individually. If you want to look at 

Not needed!

I have a Table per $USER and folders are only virtuell.  So if you have
the message in your INBOX and copy it to INBOX.pgsql the the message in
the table become two references for the TWO folders.



Note:  Currently I have 7.41 million messages in my table
   for testing plus the originals on Courier-Imap.

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
   50, rue de Soultz MSM LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: [GENERAL] PG Email Client

[Michelle Konzack]

Hello Sim,

Am 2007-01-29 14:29:27, schrieb Sim Zacks:
> For example, instead of saving a copy of an email in 1 folder, the same 
> email could be indexed to multiple folders. Current email clients mimic 
> file cabinets too much in that the system considers an email to be a 
> physical entity that can only be stored in one place. Searching for emails 
> can also be done much faster with a database then with a traditional mail 
> client approach.

Since some time I am coding an IMAP-Client which heavy search functionality
but Server based.  My base is Mutt (with GTK2 Interface), Courier-Imap,
PostgreSQL and Sieve.

The thing is currently in PreAlpha state (it works for me) with realy
limited functions,since I have excluded most thinks to reduce errors
while coding my stuff.

It is only a private Research Project but maybe some times...

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
   50, rue de Soultz MSM LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: [GENERAL] postgresql vs mysql

[Merlin Moncure]

On 2/21/07, Lincoln Yeoh  wrote:

At 07:31 PM 2/21/2007, Chad Wagner wrote:
>On 2/20/07, gustavo halperin
><[EMAIL PROTECTED]> wrote:
>I  have a friend that ask me why postgresql is better than mysql.
>I personally prefer posgresql, but she need to give in her work 3 or 4
>strong reasons for that. I mean not to much technical reasons. Can you
>give help me please ?
>
>
>How about the fact that MySQL accepts the following query as legal:
>
>SELECT foo, bar, COUNT(*)
>FROM baz
>GROUP BY foo
>
>And produces, naturally, an unexpected result instead of an
>error.  Totally annoying, I don't know if it was ever fixed.  It
>seems that MySQL's parser is generally weak at syntax validation in
>it's default configuration.

** syntax/misc gotchas

Too many. See other emails. Or search for MySQL gotchas.

** Feature gotchas
At first look MySQL seems to have all sorts of nice features and
great performance. BUT, when you start to get to the details, too
often you'd find that some features aren't so compatible with others
or take a bit (lot?) more effort to get working properly.


boy, you hit the nail on the head.  mysql supports views and
subqueries, but apparently not at the same time.  also, complex views
(such as you can write without subqueries) tend to run slower than
identical counterpart in .sql.

mysql supports pl/psm (yay) but unfortunately no FOR loops (yikes).

the mysql planner is an unpredictable thing, producing huge surprises
to the upside and the downside...however taken as a whole it is a
completely inferior planner.

merlin

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

Re: [GENERAL] Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)

[Ian Harding]

OK, here's the deal

I had views that used syntax like

WHERE datecol < current_date and (otherdatecol is null or otherdatecol

current_date)


Kind of a hillbilly version of BETWEEN but it assumes null is INFINITY
(except I use date, not timestamp)

Suddenly, this is ungodly inefficient in 8.2.3.  It worked just fine in 8.1.3.

What I had to do to "fix" it was to make a function that did the
comparison, lied and marked it immutable, and created functional
indexes using that function.  All is well now.  I would love to hear
of a more elegant solution.

Just a heads-up in case you use that type of SQL and are upgrading to
8.2.X, it might be a problem.

- Ian

On 2/21/07, Ian Harding <[EMAIL PROTECTED]> wrote:

This whole thing strikes me funny since my application has run fine
for 6 years and now I have queries that simply take forever, and even
had one that threw an error (Tom fixed the bug very quickly)  Now I
have to run with enable_nestloop off globally which seems to negate
the use of indexes by and large, and makes things slower than they
were under 8.1.3, but at least it runs.



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

  http://archives.postgresql.org/

Re: [GENERAL] Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)

[Ian Harding]

OK, here's the deal

I had views that used syntax like

WHERE datecol < current_date and (otherdatecol is null or otherdatecol

current_date)


Kind of a hillbilly version of BETWEEN but it assumes null is INFINITY
(except I use date, not timestamp)

Suddenly, this is ungodly inefficient in 8.2.3.  It worked just fine in 8.1.3.

What I had to do to "fix" it was to make a function that did the
comparison, lied and marked it immutable, and created functional
indexes using that function.  All is well now.  I would love to hear
of a more elegant solution.

Just a heads-up in case you use that type of SQL and are upgrading to
8.2.X, it might be a problem.

- Ian

On 2/21/07, Ian Harding <[EMAIL PROTECTED]> wrote:

This whole thing strikes me funny since my application has run fine
for 6 years and now I have queries that simply take forever, and even
had one that threw an error (Tom fixed the bug very quickly)  Now I
have to run with enable_nestloop off globally which seems to negate
the use of indexes by and large, and makes things slower than they
were under 8.1.3, but at least it runs.



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

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

Re: [GENERAL] postgresql vs mysql

[Peter Eisentraut]

Adam Rich wrote:
> -- Query A
> select count(*) from customers c
> where not exists ( select 1 from orders o
> where o.customer_id = c.customer_id )
>
> -- Query B
> select count(*) from customers c
> where customer_id not in ( select customer_id from orders)
>
> -- Query C
> select count(*) from customers c
> left join orders o on c.customer_id = o.customer_id
> where o.order_id is null
>
> I believe they all achieve the same thing.

I think not.  When using

INSERT INTO customers VALUES (1);
INSERT INTO customers VALUES (2);
INSERT INTO customers VALUES (NULL);

and

INSERT INTO orders VALUES (1);
INSERT INTO orders VALUES (3);
INSERT INTO orders VALUES (NULL);

I get

Query A: 2
Query B: 0
Query C: 3

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

   http://archives.postgresql.org/

[GENERAL] Quering 4 or more physicale different PostgreSQL server at once

[Michelle Konzack]

Hello,

My current PostgreSQL Database has arround 560 GByte without indices.
If not more then 3-5 user query it it works fast like Google...  :-)

But I have over 600 Users now which all query it over the Net and hit
a heavy performance problem.

The biggest problem is the "main" table which is arround 80% of the DB.

Now I have made a statistic and know, which Datas are most quried and
like to split the Database (exactly THE MONSTER-TABLE) over at least 4
Physicaly PostgreSQL servers.

The Database is generaly STATIC an Data will only be added and NEVER
removed or changed.  (If DATE or a ROW change, it will get a FULL NEW
ROW with version number)

Since I access the PostgreSQL from a pysicaly seperated dedicated
apache server using php5 I like to know from some Experts how to
query 4 or more Databases ar once and join the data.

I thinking, putting the main database with most requested data on the
first Server which will have arround 180 GByte and then the rest of
"THE" table on the other servers.

Note:  "THE" table is a historical timeline...
   and the datasets are not ordered in the table.

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
   50, rue de Soultz MSM LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

[GENERAL] Expanding the crosstab function to extra rows

[Robert Fitzpatrick]

Finally figured out what was wrong with my crosstab that I posted for
help yesterday. I was really close, just need to set the right types. I
have it working using the crosstab(text sql, int N) function. This
displays a crosstab from my view below for units sold by each sales rep
under each month...

SELECT view_pick1_months.rep, view_pick1_months."month", view_pick1_data.units,
view_pick1_data.revenue
FROM (view_pick1_months LEFT JOIN view_pick1_data ON
view_pick1_months.rep)::text = (view_pick1_data.rep)::text) AND
(view_pick1_months."month" = view_pick1_data.nmonth
ORDER BY view_pick1_months.rep, view_pick1_months."month";

primepay=# select * from view_pick1 where rep ='aespinal';
   rep| month | units | revenue
--+---+---+-
 aespinal | 1 |10 |  500
 aespinal | 2 | 9 |  100
 aespinal | 3 | 8 |  250
 aespinal | 4 | 7 |  1000
 aespinal | 5 | 6 |  500
 aespinal | 6 | 5 |  250
 aespinal | 7 | 4 |  300
 aespinal | 8 | 3 |  150
 aespinal | 9 | 2 |  100
 aespinal |10 | 1 |  250
 aespinal |11 | 2 |  5000
 aespinal |12 | 3 |  2500

In my crosstab, I only use units right now and it works fine...

primepay=# select * from crosstab('select rep, month, units from view_pick1 
where rep =''aespinal'' order by 1,2;', 12) AS view_pick1(rep varchar, jan 
bigint, feb bigint, mar bigint, apr bigint, may bigint, jun bigint, jul bigint, 
aug bigint, sep bigint, oct bigint, nov bigint, dec bigint);
   rep| jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | 
dec
--+-+-+-+-+-+-+-+-+-+-+-+-
 aespinal |  10 |   9 |   8 |   7 |   6 |   5 |   4 |   3 |   2 |   1 |   2 |   
3

Now, what I'd like to do is use the synopsis crosstab(text source_sql,
text category_sql) and include revenue on another line with units and
revenue being the extra_col in the README example for that function. So,
according to the readme, I need to produce the following data, but I am
perplexed at how to do this, can anyone help me produce the following
data:

   rep| month |  extra  | amount
--+---+-+-
 aespinal | 1 | units   | 10
 aespinal | 1 | revenue | 500
 aespinal | 2 | units   | 9
 aespinal | 2 | revenue | 100
 aespinal | 3 | units   | 8
 aespinal | 3 | revenue | 250
 aespinal | 4 | units   | 7
 aespinal | 4 | revenue | 1000
 aespinal | 5 | units   | 6
 aespinal | 5 | revenue | 500
 aespinal | 6 | units   | 5
 aespinal | 6 | revenue | 250
 aespinal | 7 | units   | 4
 aespinal | 7 | revenue | 300
 aespinal | 8 | units   | 3
 aespinal | 8 | revenue | 150
 aespinal | 9 | units   | 2
 aespinal | 9 | revenue | 100
 aespinal |10 | units   | 1
 aespinal |10 | revenue | 250
 aespinal |11 | units   | 2
 aespinal |11 | revenue | 5000
 aespinal |12 | units   | 3
 aespinal |12 | revenue | 2500

If I can accomplish the above, then I think my new crosstab would output
like this:

   rep| extra   | jan | feb | mar |  apr | may | jun | jul | aug | sep | 
oct |  nov |  dec
--+-+-+-+-+-+--+-+-+-+-+-+--+-
 aespinal |   units |  10 |   9 |   8 |7 |   6 |   5 |   4 |   3 |   2 |   
1 |2 |3
 aespinal | revenue | 500 | 100 | 250 | 1000 | 500 | 250 | 300 | 150 | 100 | 
250 | 5000 | 2500


-- 
Robert


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

Re: [GENERAL] postgresql vs mysql

[Scott Marlowe]

On Wed, 2007-02-21 at 10:54, Adam Rich wrote:
> > It's got a query parser that's dumb as a brick. 
> 
> While we're on this topic...  I have a question on these series
> of queries:
> 
> -- Query A
> select count(*) from customers c
> where not exists ( select 1 from orders o
> where o.customer_id = c.customer_id )
> 
> -- Query B
> select count(*) from customers c
> where customer_id not in ( select customer_id from orders)
> 
> -- Query C
> select count(*) from customers c
> left join orders o on c.customer_id = o.customer_id
> where o.order_id is null
> 
> 
> I believe they all achieve the same thing.  i.e. How many
> customers have never placed an order?  I ran these 3 on
> MySQL & PG with the following results:
> 
> Query A:  MySQL=4.74s  PostgreSQL=4.23s
> Query B:  MySQL=4.64s  PostgreSQL=?
> Query C:  MySQL=5.07s  PostgreSQL=3.39s
> 
> MySQL's time is pretty consistent for all 3.  As you said,
> the output from explain is pretty useless so there's not
> much else to look at.
> 
> PostgreSQL runs A&C slightly faster, which I expected.
> However, waiting for query B exceeded my patience and 
> I had to abort it.  The explain output is below, is this
> result due to some incorrect setting?

Nope, more like incorrect usage / inability to optimize by postgresql
due to architecture.  The B query (like the B arc) is a bad choice here
because PostgreSQL has to actually create a giant OR list of all the
customer_ids from order.  

But the queries I was referring to were more along the lines of multiple
level subselect queries with lots of aggregation on the outside, the
kind used for business intelligence reporting.

There might be some optimization trick for the B query I'm not familiar
with (cause every time I turn around, Tom has gone and made the query
optimizer smarter) but I haven't heard of it.

---(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] pg_dump: [tar archiver] write error appending to tar archive

[CAJ CAJ]

On 2/21/07, MG <[EMAIL PROTECTED]> wrote:


Where do I find the man page.



Try 'man pg_dump' or browse www.postgresql.org for Documentation if you
can't get to any, Google is your best friend.


- Original Message -

From: "Tom Lane" <[EMAIL PROTECTED]>
To: "MG" <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2007 3:46 PM
Subject: Re: [GENERAL] pg_dump: [tar archiver] write error appending to
tar
archive


> "MG" <[EMAIL PROTECTED]> writes:
>> could you tell me the directory where the temp files are built. It
>> doesn't
>> seem to be in /tmp.
>
> It'd be whereever tmpfile() wants to put it; see the man page for that
> function.
>
> regards, tom lane
>


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

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

Re: [GENERAL] postgresql vs mysql

[Adam Rich]

> It's got a query parser that's dumb as a brick. 

While we're on this topic...  I have a question on these series
of queries:

-- Query A
select count(*) from customers c
where not exists ( select 1 from orders o
where o.customer_id = c.customer_id )

-- Query B
select count(*) from customers c
where customer_id not in ( select customer_id from orders)

-- Query C
select count(*) from customers c
left join orders o on c.customer_id = o.customer_id
where o.order_id is null


I believe they all achieve the same thing.  i.e. How many
customers have never placed an order?  I ran these 3 on
MySQL & PG with the following results:

Query A:  MySQL=4.74s  PostgreSQL=4.23s
Query B:  MySQL=4.64s  PostgreSQL=?
Query C:  MySQL=5.07s  PostgreSQL=3.39s

MySQL's time is pretty consistent for all 3.  As you said,
the output from explain is pretty useless so there's not
much else to look at.

PostgreSQL runs A&C slightly faster, which I expected.
However, waiting for query B exceeded my patience and 
I had to abort it.  The explain output is below, is this
result due to some incorrect setting?


benchdb=# explain select count(*) from customers c
benchdb-# where customer_id not in ( select customer_id from orders);
 QUERY PLAN


 Aggregate  (cost=16406564027.00..16406564027.01 rows=1 width=0)
   ->  Seq Scan on customers c  (cost=41578.00..16406562777.00
rows=50 width=0)
 Filter: (NOT (subplan))
 SubPlan
   ->  Materialize  (cost=41578.00..69391.00 rows=200
width=4)
 ->  Seq Scan on orders  (cost=0.00..31765.00
rows=200 width=4)
(6 rows)













---(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] postgresql vs mysql

[Lincoln Yeoh]

At 12:02 AM 2/22/2007, Scott Marlowe wrote:


You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables.  Don't forget that if you've got a really


Oh yeah, that reminds me. "rewriting the whole thing" means in most 
cases the _entire_ table is temporarily _duplicated_ (with all the 
associated increased space requirements)![1]


WORSE: This happens if you are creating or deleting indexes, or even 
changing a column definition!


So say you have a 40GB table, and have 30GB free space. Life is good 
right? Then someone makes a reasonable request - Big Boss wants an 
important report sped up, and it turns out you just need to create an 
index. Enjoy :).


Running low on space and think you can get more space by deleting 
some unused indexes? Probably not a good idea!


And even if disk space is cheap, IO bandwidth usually isn't...

Regards,
Link.

[1] "If you use any option to ALTER TABLE other than RENAME, MySQL 
always creates a temporary table"


http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

MySQL: the PHP of databases.



---(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] postgresql vs mysql

[Lincoln Yeoh]

At 07:31 PM 2/21/2007, Chad Wagner wrote:
On 2/20/07, gustavo halperin 
<[EMAIL PROTECTED]> wrote:

I  have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?


How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an 
error.  Totally annoying, I don't know if it was ever fixed.  It 
seems that MySQL's parser is generally weak at syntax validation in 
it's default configuration.


** syntax/misc gotchas

Too many. See other emails. Or search for MySQL gotchas.

** Feature gotchas
At first look MySQL seems to have all sorts of nice features and 
great performance. BUT, when you start to get to the details, too 
often you'd find that some features aren't so compatible with others 
or take a bit (lot?) more effort to get working properly.


Want transactions? Use innoDB. Want to restore a multi-gigabyte 
database fast from backups, sure use MyISAM (too many people seem to 
have probs doing that with innoDB).


Want foreign keys to work? Use innoDB. MyISAM tables allow you to 
specify foreign keys but ignores AND forgets them.


You can mix MyISAM tables with innoDB tables in the same database. 
That's a minus.


Want to back up a consistent snapshot of the database AND still have 
users using the database live? Use only InnoDB tables. Because to 
ensure consistency when dumping MyISAM tables you should lock all the 
tables involved. You still want a live consistent backup of a 
database with some MyISAM tables? Here's a method I suggested: use 
multiple MySQL servers with replication - do the backup snapshot off 
a slave, while users are using the master (or other slaves). If 
anyone has better ideas do let me know :).


Do not use innoDB on a filesystem that does not support files > 2GB 
in size. Though MySQL +innoDB supports a configurable like 
"autoextend:max:1000M", this only works if you using a single shared 
tablespace, doesn't work if you are using one 
"innodb_file_per_table". BUT if you are using a single shared 
tablespace be aware that you can't easily shrink such tablespaces and 
reclaim unused space.


Too many IFs, BUTs, ONLYs, etc.

** D'oh level release gotchas
Example: Before MySQL 5.0.13, GREATEST(x,NULL) and
LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.13,
both functions return NULL if any argument is NULL, the same as
Oracle.  This change can cause problems for applications that rely
on the old behavior.

Or release 5.0.19: The InnoDB storage engine no longer ignores 
trailing spaces when comparing BINARY or VARBINARY column values. 
This means that (for example) the binary values 'a' and 'a ' are now 
regarded as unequal any time they are compared, as they are in MyISAM 
tables. (Bug#14189)


** Commercial/strategic gotchas
Oracle owns the companies that make the transactional backends for 
MySQL (innoDB, sleepycat).


** Conclusion
In my opinion, if you don't have anything that specifically requires 
MySQL, but where MySQL is suggested, it's better to use Postgresql.


Not saying Postgresql is perfect - rather that MySQL makes Postgresql 
look really good.


Unfortunately, I have to deal with MySQL at work. *sigh*.

Regards,
Link.


---(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 vs mysql

[Scott Marlowe]

On Wed, 2007-02-21 at 10:12, Jack Orenstein wrote:
> Scott Marlowe wrote:
> > You can't change a table in any way without rewriting the whole thing,
> > resulting in a very long wait and a complete table lock on any alter
> > table action on big tables.  Don't forget that if you've got a really
> > big table, you need that much space free on the drive to alter the table
> > for the rewrite that's going to take place.
> 
> Forgive a dumb question: What does postgresql do with ALTER TABLE?
> What sort of modifications do not require time proportional to the
> number of rows in the table?

It's an interesting subject, and it's not a dumb question.  In
PostgreSQL, indexes live in another file than the table.  In MySQL they
are part of the main table file with myisam tables.  I don't know what
innodb does in this regard.

The only thing I can think of that rewrites a whole postgresql table
would be reindexing it, or an update without a where clause (or a where
clause that includes every row).  Normal operations, like create index,
add column, drop column, etc do not need to rewrite the table and happen
almost instantly.

For instance, on a table with about 30 columns and 100,000 rows, I can
add a column this fast:

alter table brs add column a int;
ALTER TABLE
Time: 57.052 ms

alter table brs rename column b to c;
ALTER TABLE
Time: 33.281 ms

alter table brs drop column c;
ALTER TABLE
Time: 31.065 ms

Of course, mvcc (which both postgresql and innodb use) have other
issues, like doubling the table size if you update every row until the
dead tuples can be reclaimed.

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

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

Re: [GENERAL] postgresql vs mysql

[Jack Orenstein]

Scott Marlowe wrote:

You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables.  Don't forget that if you've got a really
big table, you need that much space free on the drive to alter the table
for the rewrite that's going to take place.


Forgive a dumb question: What does postgresql do with ALTER TABLE?
What sort of modifications do not require time proportional to the
number of rows in the table?

Jack Orenstein

---(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] postgresql vs mysql

[Scott Marlowe]

On Tue, 2007-02-20 at 15:25, gustavo halperin wrote:
>   Hello
> 
>  I  have a friend that ask me why postgresql is better than mysql.
>  I personally prefer posgresql, but she need to give in her work 3 or 4 
> strong reasons for that. I mean not to much technical reasons. Can you 
> give help me please ?

My personal peeves:

It's got a query parser that's dumb as a brick.  Basically it seems to
work like this:  Got an index?  Cool, use it.  Complex queries quickly
bog down on large data sets in MySQL.  Just read the database forums at
phpbuilder.com or anywhere else that people use mysql a lot and you'll
see request after request to fix up a query performance-wise that
PostgreSQL would run with decent speed.  Further, the output of Explain
is damned near useless.

You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables.  Don't forget that if you've got a really
big table, you need that much space free on the drive to alter the table
for the rewrite that's going to take place.

It swallows column level foreign key contraints and does nothing with
them, no errors nothing, even if you're defining innodb tables.  I.e.
this produces not errors:

mysql> create table a (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
 
mysql> create table b (a_id int references a(id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into a values (1);
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into b values (1);
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into b values (2);
Query OK, 1 row affected (0.03 sec)
 
That last statement should fail.  Or the creation of table b should
throw a warning.  Or something.

This is with 5.0.19.

So, innodb tables pay for the sins of the fathers (i.e. myisam tables)
and by extension, so do you.

My main gripe about MySQL is that it teaches you bad habits.  It plays
loose and fast with your data, and teaches you to do that too.

If it was a lot faster than PostgreSQL (like it was back in the days of
7.1 or 7.2) it might be worth the effort to overcome its shortcomings,
but it's not.

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

Re: [GENERAL] tsearch2: word position

[Markus Schiltknecht]

Hello Teodor,

Teodor Sigaev wrote:

It's not supposed usage... Why do you need that?


Well, long story... I'm still using my own indexing on top of the 
tsearch2 parsers and stemming.


However, two obvious cases come to mind:

- autocompletion, where I want to give the user one of the possible 
known words. Currently, I'm returning the stemmed word, which is 
obviously not quite right.


- highlighting of matching words

Have a look to headline framework as an example or staring point. 
hlparsetext() returns  parsed text with matched lexemes in tsquery. 
Small description of hlparsetext is placed at 
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/HOWTO-parser-tsearch2.html 
near the end. Description of HLWORD struct is some out of day, sorry.


Thanks. I probably need to dig in the sources, though.

Markus


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

Re: [GENERAL] tsearch2: word position

[Teodor Sigaev]

I'm fiddling with to_tsvector() and parse() from tsearch2, trying to get 
the word position from those functions. I'd like to use the tsearch2 
parser and stemmer, but I need to know the exact position of the word as 
well as the original, unstemmed word.


It's not supposed usage... Why do you need that?

And this only tells me a word position, not a character or byte position 
within the string. Is there a way to get this information from tsearch2?


Have a look to headline framework as an example or staring point. hlparsetext() 
returns  parsed text with matched lexemes in tsquery. Small description of 
hlparsetext is placed at 
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/HOWTO-parser-tsearch2.html

near the end. Description of HLWORD struct is some out of day, sorry.

--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

  http://archives.postgresql.org/

Re: [GENERAL] REVOKE ALL

[Alvaro Herrera]

David Legault escribió:

> It's marked with an % saying it's easy to implement, but isn't with a "-" so
> it won't be in 8.3.

Note that having a "-" in front means "somebody already coded it and the
patch has been committed".  Not having it does not mean it won't be in
8.3; it means nobody has done it _yet_.  So you still have a chance to
put the "-" in there before the 8.3 development cycle ends, if you have
sufficient motivation.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] Slow running to_tsvector (tsearch2 in PG 8.2.3)

[cedric]

Le mercredi 21 février 2007 15:44, Tom Lane a écrit :
> Markus Schiltknecht <[EMAIL PROTECTED]> writes:
> > Henrik Zagerholm wrote:
> >> Is this normal? What can I tweak in postgresql.conf to speed up big
> >> to_tsvector()?
> >
> > Hm.. seems not too unreasonable to me.
> > Take a look at the stemmers or dictionaries involved. What do you use
> > there?
>
> Also, I wonder how much of the time went into to_tsvector() and how much
> into updating the GIST or GIN index that I suppose is on the column.
> For a full update like this it might make sense to drop the index,
> update the column, rebuild the index from scratch.
a count of distinct word in those 194 000 can be interesting also, I presume.
>
>   regards, tom lane
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

---(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] Views: having a rule call a function vs. using a before trigger

[Ken Downs]

"Karl O. Pinc" <[EMAIL PROTECTED]> wrote:  > Hi, >  >
Postgresql 8.1. >  > I'm trying to come up with a generic way > of
inserting into a view, particularly regards > error testing and the
generation of complicated > foreign keys.  I don't seem to be having much
luck. >  > (I also want to update and delete, but haven't gotten >
that far.)
Before I mount too much of a reply, may I ask in particularwhy you are
targeting views?  Reason I ask is that I've gonethrough the same process and
have worked out a generalapproach to implementing all biz logic in
triggers.However, as the biz logic is defined in terms of tables, weattached
all logic always to tables. When for whateverreason the user is going through
a view, the rule sends itback to the base table where the biz logic takes care
of itthere.This avoids attempting to redefine or recast the rules inthe
context of any particular view.So that is why I am asking why in particular
you are asking about views.
>  > I thought that, for inserts at least, I could write > a rule
that called a function and have the function > do anything complicated,
raise exceptions, etc. > But I get the error message shown below. > 
_
This mail sent using V-webmail - http://www.v-webmail.org

[GENERAL] How can you tell if a function is immutable from psql?

[Michael Nolan]

Am I just missing it or is there no way to tell if a function is noted as
immutable from the \df or \df+ output in psql?
--
Mike Nolan

[GENERAL] tsearch2: word position

[Markus Schiltknecht]

Hi,

I'm fiddling with to_tsvector() and parse() from tsearch2, trying to get 
the word position from those functions. I'd like to use the tsearch2 
parser and stemmer, but I need to know the exact position of the word as 
well as the original, unstemmed word.


What I came up with so far is pretty ugly:

SELECT
  (parse('my test text')).tokid,
  (parse('my test text')).token,
  strip(to_tsvector((parse('my test text')).token));

And this only tells me a word position, not a character or byte position 
within the string. Is there a way to get this information from tsearch2?


Regards

Markus

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

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

[Henrik Zagerholm]

21 feb 2007 kl. 15:44 skrev Tom Lane:


Markus Schiltknecht <[EMAIL PROTECTED]> writes:

Henrik Zagerholm wrote:

Is this normal? What can I tweak in postgresql.conf to speed up big
to_tsvector()?



Hm.. seems not too unreasonable to me.
Take a look at the stemmers or dictionaries involved. What do you  
use there?
I tried both the default english and simple stemmers. Simple was a  
little bit faster but not much.



What is your configuration ? to_tsvector does a lot of work.
I haven't tweaked the postgresql.conf yet. What should I focus on?  
work_mem, shared_buffers?


Also, I wonder how much of the time went into to_tsvector() and how  
much

into updating the GIST or GIN index that I suppose is on the column.
For a full update like this it might make sense to drop the index,
update the column, rebuild the index from scratch.
I actually tested to remove the GIN index on the vectors column and  
it didn't have any noticeable effect. Maybe because its the only  
tuple in the database.


Thanks for all your input!

regards, henrik



regards, tom lane

---(end of  
broadcast)---

TIP 5: don't forget to increase your free space map settings



---(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] REVOKE ALL

[David Legault]

Will report on it,

There isn't anything in the FAQ about changes to the ROLES that I've seen

though this line may be interesting:

%Allow GRANT/REVOKE permissions to be applied to all schema objects with one
command

The proposed syntax is:

GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW TABLES
IN public TO phpuser;
It's marked with an % saying it's easy to implement, but isn't with a "-" so
it won't be in 8.3.

Will make REVOKE and GRANT commands much easier without knowing all the
objects that need to be listed to use them.

Thanks

David

On 2/21/07, Richard Huxton  wrote:


David Legault wrote:
> I'll test all that [EMAIL PROTECTED] stuff and see what happens and if it 
works
> properly and report back on it.

Excellent - never used it myself.

> Is there a place where I can see what features were suggested and where
I
> could suggest my own?

Full list, and what people are thinking about for 8.3:
   http://www.postgresql.org/docs/faqs.TODO.html
   http://developer.postgresql.org/index.php/Todo:WishlistFor83

Check the mailing-list archives before proposing a new feature - see
what others have thought of in the past. Then, think through what you
want and discuss it here before posting to the hackers list. If you're
not planning to make changes yourself, you'll either need to directly
persuade a developer, or gain enough support to convince one it's worth
their time.

--
   Richard Huxton
   Archonet Ltd

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

[Tom Lane]

Markus Schiltknecht <[EMAIL PROTECTED]> writes:
> Henrik Zagerholm wrote:
>> Is this normal? What can I tweak in postgresql.conf to speed up big 
>> to_tsvector()?

> Hm.. seems not too unreasonable to me.
> Take a look at the stemmers or dictionaries involved. What do you use there?

Also, I wonder how much of the time went into to_tsvector() and how much
into updating the GIST or GIN index that I suppose is on the column.
For a full update like this it might make sense to drop the index,
update the column, rebuild the index from scratch.

regards, tom lane

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

Re: [GENERAL] postgresql vs mysql

[Michael Fuhr]

On Wed, Feb 21, 2007 at 08:54:30AM -0500, Jan de Visser wrote:
> It gets better: The problem is not just feb 35, it's also that it doesn't 
> warn 
> you that it didn't like the input format:

Actually it did, sort of.

> mysql> insert into test values ('35-Feb-2007');
> Query OK, 1 row affected, 1 warning (0.07 sec)
^
mysql> show warnings;
+-+--+-+
| Level   | Code | Message |
+-+--+-+
| Warning | 1265 | Data truncated for column 'td' at row 1 | 
+-+--+-+
1 row in set (0.00 sec)

Not as good as "ERROR: hey bonehead, there ain't no such date" but
at least it's something :-)

-- 
Michael Fuhr

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

Re: [GENERAL] Odd behaviour of timestamptz

[Tom Lane]

Matteo Beccati <[EMAIL PROTECTED]> writes:
> test=# INSERT into test values ('1910-01-10');
> INSERT 0 1

> test=# INSERT into test values ('1990-01-10');
> INSERT 0 1

> test=# SELECT * from test;
>data
> 
> 1910-01-10 00:00:00+00:19:32
> 1990-01-10 00:00:00+01
> (2 rows)

I'll bet you are running in Europe/Amsterdam time zone?  The above is
correct behavior according to the zic data files (see below).

regards, tom lane


# Netherlands

# Howse writes that the Netherlands' railways used GMT between 1892 and 1940,
# but for other purposes the Netherlands used Amsterdam mean time.

# However, Robert H. van Gent writes (2001-04-01):
# Howse's statement is only correct up to 1909. From 1909-05-01 (00:00:00
# Amsterdam mean time) onwards, the whole of the Netherlands (including
# the Dutch railways) was required by law to observe Amsterdam mean time
# (19 minutes 32.13 seconds ahead of GMT). This had already been the
# common practice (except for the railways) for many decades but it was
# not until 1909 when the Dutch government finally defined this by law.
# On 1937-07-01 this was changed to 20 minutes (exactly) ahead of GMT and
# was generally known as Dutch Time ("Nederlandse Tijd").
#
# (2001-04-08):
# 1892-05-01 was the date when the Dutch railways were by law required to
# observe GMT while the remainder of the Netherlands adhered to the common
# practice of following Amsterdam mean time.
#
# (2001-04-09):
# In 1835 the authorities of the province of North Holland requested the
# municipal authorities of the towns and cities in the province to observe
# Amsterdam mean time but I do not know in how many cases this request was
# actually followed.
#
# From 1852 onwards the Dutch telegraph offices were by law required to
# observe Amsterdam mean time. As the time signals from the observatory of
# Leiden were also distributed by the telegraph system, I assume that most
# places linked up with the telegraph (and railway) system automatically
# adopted Amsterdam mean time.
#
# Although the early Dutch railway companies initially observed a variety
# of times, most of them had adopted Amsterdam mean time by 1858 but it
# was not until 1866 when they were all required by law to observe
# Amsterdam mean time.

# The data before 1945 are taken from
# .

# Rule  NAMEFROMTO  TYPEIN  ON  AT  SAVELETTER/S
RuleNeth1916only-   May  1  0:001:00NST 
# Netherlands Summer Time
RuleNeth1916only-   Oct  1  0:000   AMT 
# Amsterdam Mean Time
RuleNeth1917only-   Apr 16  2:00s   1:00NST
RuleNeth1917only-   Sep 17  2:00s   0   AMT
RuleNeth19181921-   Apr Mon>=1  2:00s   1:00NST
RuleNeth19181921-   Sep lastMon 2:00s   0   AMT
RuleNeth1922only-   Mar lastSun 2:00s   1:00NST
RuleNeth19221936-   Oct Sun>=2  2:00s   0   AMT
RuleNeth1923only-   Jun Fri>=1  2:00s   1:00NST
RuleNeth1924only-   Mar lastSun 2:00s   1:00NST
RuleNeth1925only-   Jun Fri>=1  2:00s   1:00NST
# From 1926 through 1939 DST began 05-15, except that it was delayed by a week
# in years when 05-15 fell in the Pentecost weekend.
RuleNeth19261931-   May 15  2:00s   1:00NST
RuleNeth1932only-   May 22  2:00s   1:00NST
RuleNeth19331936-   May 15  2:00s   1:00NST
RuleNeth1937only-   May 22  2:00s   1:00NST
RuleNeth1937only-   Jul  1  0:001:00S
RuleNeth19371939-   Oct Sun>=2  2:00s   0   -
RuleNeth19381939-   May 15  2:00s   1:00S
RuleNeth1945only-   Apr  2  2:00s   1:00S
RuleNeth1945only-   Sep 16  2:00s   0   -
#
# Amsterdam Mean Time was +00:19:32.13 exactly, but the .13 is omitted
# below because the current format requires GMTOFF to be an integer.
# Zone  NAMEGMTOFF  RULES   FORMAT  [UNTIL]
Zone Europe/Amsterdam   0:19:32 -   LMT 1835
0:19:32 Neth%s  1937 Jul  1
0:20NethNE%sT   1940 May 16 0:00 # Dutch Time
1:00C-Eur   CE%sT   1945 Apr  2 2:00
1:00NethCE%sT   1977
1:00EU  CE%sT

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

Re: [GENERAL] postgresql vs mysql

[Jan de Visser]

On Wednesday 21 February 2007 1:10:41 am Tom Lane wrote:
> "Adam Rich" <[EMAIL PROTECTED]> writes:
> > I'm not apologizing for their past mistakes.. But the issue
> > you cite is no longer true:
> > "As of 5.0.2, the server requires that month and day values
> > be legal, and not merely in the range 1 to 12 and 1 to 31,
> > respectively."
>
> Really?
>
> [EMAIL PROTECTED] ~]$ mysql test
... snip ...

It gets better: The problem is not just feb 35, it's also that it doesn't warn 
you that it didn't like the input format:

[head sep-head 08:49]$ mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.33 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from test;
++
| td |
++
| -00-00 |
++
1 row in set (0.00 sec)

mysql> insert into test values ('17-Feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
++
| td |
++
| -00-00 |
| -00-00 |
++
2 rows in set (0.01 sec)

mysql> insert into test values ('2007-02-19');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
++
| td |
++
| -00-00 |
| -00-00 |
| 2007-02-19 |
++
3 rows in set (0.00 sec)

mysql> insert into test values ('2007-02-35');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
++
| td |
++
| -00-00 |
| -00-00 |
| 2007-02-19 |
| -00-00 |
++
4 rows in set (0.00 sec)

mysql>



-- 
--
Jan de Visser                     [EMAIL PROTECTED]

                Baruk Khazad! Khazad ai-menu!
--

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

Re: [GENERAL] REVOKE ALL

[Richard Huxton]

David Legault wrote:

I'll test all that [EMAIL PROTECTED] stuff and see what happens and if it works
properly and report back on it.


Excellent - never used it myself.


Is there a place where I can see what features were suggested and where I
could suggest my own?


Full list, and what people are thinking about for 8.3:
  http://www.postgresql.org/docs/faqs.TODO.html
  http://developer.postgresql.org/index.php/Todo:WishlistFor83

Check the mailing-list archives before proposing a new feature - see 
what others have thought of in the past. Then, think through what you 
want and discuss it here before posting to the hackers list. If you're 
not planning to make changes yourself, you'll either need to directly 
persuade a developer, or gain enough support to convince one it's worth 
their time.


--
  Richard Huxton
  Archonet Ltd

---(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] WARNING: some databases have not been vacuumed in 1953945422 transactions

[Albe Laurenz]

MG wrote:
>> I don't think that there were any changes to the template databases.
>> 
>> You detected a difference in age(datfrozenxid) - try selecting
>> datfrozenxid itself and you will probably see that it does not
>> change over time.
>
> Yes there are changes.
> db1, db2 and template1 has been vacuumed over night, not template0.
>  20.02.07 datname - (12) age - (4)
>   1 db1 1.075.878.187
>   2 db2 1.075.847.556
>   3 template1 1.976.569.889
>   4 template0 1.976.569.889
> 
> 
> 
> 
>   21.02.07 datname - (12) age - (4)
>   1 db1 1.074.758.205
>   2 db2 1.074.728.832
>   3 template1 1.074.728.720
>   4 template0 1.978.965.587

What do you mean by (12) age - (4)?

I told you to select datfrozenxid and not age(datfrozenxid).

Try and vacuum ALL databases.

Yours,
Laurenz Albe

---(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] REVOKE ALL

[David Legault]

I only have a local root user for DBA pruposes, the rest will be DB specific
roles (If I can do it properly).

Users have no privileges except CONNECT to the DB. All the privileges are
granted to group roles.

Users are assigned groups to have access to DB functionality which are all
stored in functions.

There is a "guests" group role for public access which is locked down to the
basic data stuff, minimum required to display stuff on the website. Then
there is "administrators" group role for the admin of the system. A user
member of that group can then start creating new groups add assign them
privileges (application + DB - they are synched) and then create new users
and assign them groups.

Everything is built so that the modularity of the application side can be
respected, I just need to drop in a new application module, update the
application privileges table, add the new functions and sync them together
(M to M table mapping) and the system will now detect the new possibilities
itself and allow an admin to add the new module privileges to already
existing groups or create a new group for them.

I'll test all that [EMAIL PROTECTED] stuff and see what happens and if it works
properly and report back on it.

Is there a place where I can see what features were suggested and where I
could suggest my own?

Thanks

David

On 2/21/07, Richard Huxton  wrote:


David Legault wrote:
>  From the docs
>
> db_user_namespace (boolean)

> It doesn't talk about this in the CREATE ROLE docs though so it's a bit
> ambiguous and the note saying this is a temp measure means they are
> thinking
> of something better for the future I assume.

Well, it's clearly not ideal, but it's probably a fair bit of work to
have global AND database-specific users in a clean way, and there's just
not been the demand for it.

> I'm trying to build a web managed system for my apps (PHP) where I can
> manage ROLES (users/groups) using an admin area of the site without
having
> to touch the DB directly like a DBA. This enables the customer to set
> himself the access levels of the groups which are assigned to their
> different users. Restricting roles to databases is what I want
> ultimately. I
> use the DB role system as the auth mecanism of the website too, so no
> actual
> DB super user has access to the DB and is stored in server PHP code. If
the
> server is compromised, there is still another layer before reaching the
DB
> data.

Hmm - never gone quite that far myself. You've got to balance the
prospects of someone gaining access to your PHP code versus the risks of
handing out database passwords to all your users.

Recently I've been using one user my app connects as, then use SET ROLE
to switch to individual user-types or users. Not proof against hackers
(except the most stupid), but it does prevent e.g. accidental changes to
lookup tables. It'd be nice to have a LOCK option on the SET ROLE, but
again, it depends on demand.

Then, I have a separate user who owns the database and I use that user
for admin tasks (backups etc).

> If there was an option to force each new ROLE to have no connexion
> privileges to any DB until I set one via GRANT would also be good. Else
> I'll
> have to revoke all DBs when I create it and then GRANT only the single
> one I
> want.

Yep - REVOKE public from all databases and then anything after that will
have to be a member of a group you've explicitly GRANTed.

--
   Richard Huxton
   Archonet Ltd

Re: [GENERAL] REVOKE ALL

[Richard Huxton]

David Legault wrote:

 From the docs

db_user_namespace (boolean)



It doesn't talk about this in the CREATE ROLE docs though so it's a bit
ambiguous and the note saying this is a temp measure means they are 
thinking

of something better for the future I assume.


Well, it's clearly not ideal, but it's probably a fair bit of work to 
have global AND database-specific users in a clean way, and there's just 
not been the demand for it.



I'm trying to build a web managed system for my apps (PHP) where I can
manage ROLES (users/groups) using an admin area of the site without having
to touch the DB directly like a DBA. This enables the customer to set
himself the access levels of the groups which are assigned to their
different users. Restricting roles to databases is what I want 
ultimately. I
use the DB role system as the auth mecanism of the website too, so no 
actual

DB super user has access to the DB and is stored in server PHP code. If the
server is compromised, there is still another layer before reaching the DB
data.


Hmm - never gone quite that far myself. You've got to balance the 
prospects of someone gaining access to your PHP code versus the risks of 
handing out database passwords to all your users.


Recently I've been using one user my app connects as, then use SET ROLE 
to switch to individual user-types or users. Not proof against hackers 
(except the most stupid), but it does prevent e.g. accidental changes to 
lookup tables. It'd be nice to have a LOCK option on the SET ROLE, but 
again, it depends on demand.


Then, I have a separate user who owns the database and I use that user 
for admin tasks (backups etc).



If there was an option to force each new ROLE to have no connexion
privileges to any DB until I set one via GRANT would also be good. Else 
I'll
have to revoke all DBs when I create it and then GRANT only the single 
one I

want.


Yep - REVOKE public from all databases and then anything after that will 
have to be a member of a group you've explicitly GRANTed.


--
  Richard Huxton
  Archonet Ltd

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

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

Re: [GENERAL] REVOKE ALL

[David Legault]

From the docs


db_user_namespace (boolean)

This parameter enables per-database user names. It is off by default. This
parameter can only be set in the postgresql.conf file or on the server
command line.

If this is on, you should create users as [EMAIL PROTECTED] When username is
passed by a connecting client, @ and the database name are appended to the
user name and that database-specific user name is looked up by the server.
Note that when you create users with names containing @ within the SQL
environment, you will need to quote the user name.

With this parameter enabled, you can still create ordinary global users.
Simply append @ when specifying the user name in the client. The @ will be
stripped off before the user name is looked up by the server.

*Note: * This feature is intended as a temporary measure until a complete
solution is found. At that time, this option will be removed.

If I read it properly, this means I can create the ROLES as [EMAIL PROTECTED] 
and they
will be limited to the DB to which is contained in their ROLE name.

It doesn't talk about this in the CREATE ROLE docs though so it's a bit
ambiguous and the note saying this is a temp measure means they are thinking
of something better for the future I assume.

I'm trying to build a web managed system for my apps (PHP) where I can
manage ROLES (users/groups) using an admin area of the site without having
to touch the DB directly like a DBA. This enables the customer to set
himself the access levels of the groups which are assigned to their
different users. Restricting roles to databases is what I want ultimately. I
use the DB role system as the auth mecanism of the website too, so no actual
DB super user has access to the DB and is stored in server PHP code. If the
server is compromised, there is still another layer before reaching the DB
data.

If there was an option to force each new ROLE to have no connexion
privileges to any DB until I set one via GRANT would also be good. Else I'll
have to revoke all DBs when I create it and then GRANT only the single one I
want.

Thanks

David

On 2/21/07, Richard Huxton  wrote:


David Legault wrote:
> Concerning the pg_hba.conf file, I don't want to prevent external
> connections to the DB as I need all my web apps to connect to them. I
was
> referring the fact that ROLE A "belongs" to DB G so that I don't want
> him to
> access anything in DB H for example.
>
> I'd like to be able to create roles that can't connect (not the
pg_hba.conf
> user configs) to any database except the ones for which they have been
> granted the privilege to do so.
>
> So when creating ROLE A, he wouldn't be able to connect [through a PHP
call
> pg_connect(user, pass, db)] until I explicitly grant him access to the
DB
> for connexion via GRANT ON DATABASE G TO A.

REVOKE CONNECT ON DATABASE g FROM public;

Then you'll need to add "GRANT CONNECT" for each user/group.

Also check the section on database-specific roles in the manuals
(there's a setting in postgresql.conf that lets you have [EMAIL PROTECTED]).
Might be useful.

--
   Richard Huxton
   Archonet Ltd

Re: [GENERAL] Odd behaviour of timestamptz

[Martijn van Oosterhout]

On Wed, Feb 21, 2007 at 09:40:05AM +0100, Matteo Beccati wrote:
> > test=# SELECT * from test;
> >data
> > ---
> >  1910-01-10 00:00:00+01:24
> >  1990-01-10 00:00:00+01
> > (2 rows)
> 
> Actually this one was built from source (CVS HEAD from yesterday), so
> it's not a packaging issue as I first was thinking.

Hmm, 1910 is before the time that timezones where spaced in nice
intervals of an hour, and there where plenty of places in the world
that had very odd offsets relative to what is used today.

Between 1909 and 1937 Amsterdam was 19 minutes 32.13 seconds ahead of
GMT, so I imagine entiring a time in that period will produce similar
results.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] REVOKE ALL

[Richard Huxton]

David Legault wrote:

Concerning the pg_hba.conf file, I don't want to prevent external
connections to the DB as I need all my web apps to connect to them. I was
referring the fact that ROLE A "belongs" to DB G so that I don't want 
him to

access anything in DB H for example.

I'd like to be able to create roles that can't connect (not the pg_hba.conf
user configs) to any database except the ones for which they have been
granted the privilege to do so.

So when creating ROLE A, he wouldn't be able to connect [through a PHP call
pg_connect(user, pass, db)] until I explicitly grant him access to the DB
for connexion via GRANT ON DATABASE G TO A.


REVOKE CONNECT ON DATABASE g FROM public;

Then you'll need to add "GRANT CONNECT" for each user/group.

Also check the section on database-specific roles in the manuals 
(there's a setting in postgresql.conf that lets you have [EMAIL PROTECTED]). 
Might be useful.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] REVOKE ALL

[David Legault]

Concerning the pg_hba.conf file, I don't want to prevent external
connections to the DB as I need all my web apps to connect to them. I was
referring the fact that ROLE A "belongs" to DB G so that I don't want him to
access anything in DB H for example.

I'd like to be able to create roles that can't connect (not the pg_hba.conf
user configs) to any database except the ones for which they have been
granted the privilege to do so.

So when creating ROLE A, he wouldn't be able to connect [through a PHP call
pg_connect(user, pass, db)] until I explicitly grant him access to the DB
for connexion via GRANT ON DATABASE G TO A.

As for the REVOKE and checking of privileges, haven't found anything for
that on pgfoundry, will look on google.

Thanks

David

On 2/21/07, Richard Huxton  wrote:


David Legault wrote:
> In which table pg_* are stored the GRANT options? As I can do a
cross-check
> with a SELECT to see if the user has any grants on functions using the
> pg_proc table. At the same time, I need to know exactly the names of the
> functions to be able to REVOKE them which in my opinion, there should be
a
> wildcard which enables you to REVOKE everything at once without prior
> knowing the names of the functions.

Check pgfoundry / google for some functions to do just that - there are
plenty out there.

If you want to write your own, the permissions are stored in "proacl" in
pg_proc (and similarly named columns in other tables for other objects).
You may find the information_schema.routine* views simpler to work with
for part of your effort though.

--
   Richard Huxton
   Archonet Ltd

Re: [GENERAL] WARNING: some databases have not been vacuumed in 1953945422 transactions

[Richard Huxton]

MG wrote:

Yes there are changes.
db1, db2 and template1 has been vacuumed over night, not template0.



 4 template0 1.976.569.889



 4 template0 1.978.965.587


Not necessarily - the age is the count of how many transactions have 
occurred ACROSS ALL DATABASES since the "datfrozenxid" in that database.


--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] WARNING: some databases have not been vacuumed in 1953945422 transactions

[MG]

Yes there are changes.
db1, db2 and template1 has been vacuumed over night, not template0.
20.02.07 datname - (12) age - (4)
 1 db1 1.075.878.187
 2 db2 1.075.847.556
 3 template1 1.976.569.889
 4 template0 1.976.569.889




 21.02.07 datname - (12) age - (4)
 1 db1 1.074.758.205
 2 db2 1.074.728.832
 3 template1 1.074.728.720
 4 template0 1.978.965.587


Regards
Michaela


- Original Message - 
From: "Albe Laurenz" <[EMAIL PROTECTED]>

To: "MG *EXTERN*" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, February 19, 2007 9:41 AM
Subject: Re: [GENERAL] WARNING: some databases have not been vacuumed in 
1953945422 transactions




But I don't understand why there are changes of the databases
template1 and
template0 at all?
I thought they are only templates.


I don't think that there were any changes to the template databases.

You detected a difference in age(datfrozenxid) - try selecting
datfrozenxid
itself and you will probably see that it does not change over time.

Yours,
Laurenz Albe

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

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


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

Re: [GENERAL] REVOKE ALL

[Richard Huxton]

David Legault wrote:

In which table pg_* are stored the GRANT options? As I can do a cross-check
with a SELECT to see if the user has any grants on functions using the
pg_proc table. At the same time, I need to know exactly the names of the
functions to be able to REVOKE them which in my opinion, there should be a
wildcard which enables you to REVOKE everything at once without prior
knowing the names of the functions.


Check pgfoundry / google for some functions to do just that - there are 
plenty out there.


If you want to write your own, the permissions are stored in "proacl" in 
pg_proc (and similarly named columns in other tables for other objects). 
You may find the information_schema.routine* views simpler to work with 
for part of your effort though.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

[Oleg Bartunov]

On Wed, 21 Feb 2007, Henrik Zagerholm wrote:


Hello list,

I've been testing tsearch2 for a while and I recently noticed some really 
slow queries.


This is a quite big document so the times are maybe accurate.

The document has about 194 000 words.

I put all the data in tbl_fulltext.fulltext_text and then did a

update tbl_fulltext set vectors = to_tsvector(fulltext_text);

Which takes about 80 seconds to complete.
The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory.

Is this normal? What can I tweak in postgresql.conf to speed up big 
to_tsvector()?


What is your configuration ? to_tsvector does a lot of work.



Regards,
Henrik


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


Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(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 vs mysql

[Russ Brown]

This can (I discovered yesterday) be fixed by adding ONLY_FULL_GROUP_BY 
to the sql_mode setting.


As Ron mentioned though that can be happily overridden on a per-session 
basis so it's not as 'strict' as it makes out...


Chad Wagner wrote:
On 2/20/07, *gustavo halperin* <[EMAIL PROTECTED] 
> wrote:


I  have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?


How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an error.  
Totally annoying, I don't know if it was ever fixed.  It seems that 
MySQL's parser is generally weak at syntax validation in it's default 
configuration.



--
Chad
http://www.postgresqlforums.com/



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

  http://archives.postgresql.org/

Re: [GENERAL] pg_dump: [tar archiver] write error appending to tar archive

[MG]

Where do I find the man page.

Regards
Michaela

- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>

To: "MG" <[EMAIL PROTECTED]>
Sent: Tuesday, February 13, 2007 3:46 PM
Subject: Re: [GENERAL] pg_dump: [tar archiver] write error appending to tar 
archive




"MG" <[EMAIL PROTECTED]> writes:
could you tell me the directory where the temp files are built. It 
doesn't

seem to be in /tmp.


It'd be whereever tmpfile() wants to put it; see the man page for that
function.

regards, tom lane




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

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

Re: [GENERAL] REVOKE ALL

[David Legault]

In which table pg_* are stored the GRANT options? As I can do a cross-check
with a SELECT to see if the user has any grants on functions using the
pg_proc table. At the same time, I need to know exactly the names of the
functions to be able to REVOKE them which in my opinion, there should be a
wildcard which enables you to REVOKE everything at once without prior
knowing the names of the functions.

The has_function_privilege(user, function, privilege) is of no use except to
check if he has a GRANT on a function but again you need to explicitly name
that function and arguments when you REVOKE.

I'll then block everyone on connection, and allocate to new users using the
grant options on database.

Thanks

David

On 2/21/07, Richard Huxton  wrote:


David Legault wrote:
> Hello,
>
> Is there a way to revoke all privileges of a role without actually
> specifying the whole list of items.
>
> Like if a role has privileges on FUNCTIONs, is there a REVOKE all
> FUNCTIONS.

There's no GRANT/REVOKE  ON public.* command format, but there are
plenty of plpgsql functions that do something of the sort.

> Is there a way to check if it has a GRANT in a particular type (CONNECT,
> FUNCTION, TRIGGER) before calling the REVOKE command?

You can wrap it in a function and check the system catalogues or use the
has_xxx_privilege() functions, otherwise no.

> Also, if I do a GRANT CONNECT ON DATABASE X TO Y, will Y be able to
connect
> to other databases if I haven't given him permission to do so (what is
the
> default value when a role is created since roles are global)?

By default all users can connect to all databases. This is limited by
your pg_hba.conf settings and after that by GRANT CONNECT;

--
   Richard Huxton
   Archonet Ltd

Re: [GENERAL] postgresql vs mysql

[Chad Wagner]

On 2/20/07, gustavo halperin <[EMAIL PROTECTED]> wrote:


I  have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?



How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an error.  Totally
annoying, I don't know if it was ever fixed.  It seems that MySQL's parser
is generally weak at syntax validation in it's default configuration.


--
Chad
http://www.postgresqlforums.com/

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

[Markus Schiltknecht]

Hi,

Henrik Zagerholm wrote:

Which takes about 80 seconds to complete.
The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory.

Is this normal? What can I tweak in postgresql.conf to speed up big 
to_tsvector()?


Hm.. seems not too unreasonable to me.

Take a look at the stemmers or dictionaries involved. What do you use there?

Regards

Markus

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

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

Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

[Richard Huxton]

Henrik Zagerholm wrote:

The document has about 194 000 words.



update tbl_fulltext set vectors = to_tsvector(fulltext_text);

Which takes about 80 seconds to complete.
The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory.

Is this normal? What can I tweak in postgresql.conf to speed up big 
to_tsvector()?


Is this a problem? How many 194,000 word documents do you index? If you 
have many, you might want to separate them into chapters. I think 
tsearch2 is more targetted at web-pages or short PDFs rather than whole 
books.


--
  Richard Huxton
  Archonet Ltd

---(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

[GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)

[Henrik Zagerholm]

Hello list,

I've been testing tsearch2 for a while and I recently noticed some  
really slow queries.


This is a quite big document so the times are maybe accurate.

The document has about 194 000 words.

I put all the data in tbl_fulltext.fulltext_text and then did a

update tbl_fulltext set vectors = to_tsvector(fulltext_text);

Which takes about 80 seconds to complete.
The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory.

Is this normal? What can I tweak in postgresql.conf to speed up big  
to_tsvector()?


Regards,
Henrik


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

Re: [GENERAL] Odd behaviour of timestamptz

[hubert depesz lubaczewski]

On 2/21/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:


I suspect your RPMs build PostgreSQL without --enable-integer-
datetimes. Without this configure flag, timestamps are represented as
floats, with all of the imprecision that implies. See the second note
below the Date/Time Types table:



doesn't seem to be relevant.
i got brand new cvs head (about 40 minutes ago). configured it with:
./configure \
   --prefix=/home/pgdba/work \
   --with-pgport=5810 \
   --with-tcl \
   --with-perl \
   --with-python \
   --enable-integer-datetimes \
   --without-krb5 \
   --without-pam \
   --without-bonjour \
   --with-openssl \
   --with-readline \
   --with-zlib \
   --with-gnu-ld

and rerun the test:
# create TABLE test (data timestamptz);
CREATE TABLE

# INSERT into test values ('1910-01-10');
INSERT 0 1

# INSERT into test values ('1990-01-10');
INSERT 0 1

# select * from test;
  data
---
1910-01-10 00:00:00+01:24
1990-01-10 00:00:00+01
(2 rows)


still something's wrong.

depesz

Re: [GENERAL] number of tables limited over time (not simultaneous)?

[Richard Huxton]

dave crane wrote:
We've settled upon a method for gathering raw statistics from widely 
scattered data centers of creating one sequence per-event, per minute.


Aside from security concerns, did we miss something?  Should I be 
worried we're going through ~60,000 sequences per day?


Well, you'll want to make sure you're vacuuming the system tables 
regularly. And you might want to make sure your code can retry creating 
the sequences once OIDs wrap around.


Not sure I'd have thought of doing things this way - I'd probably have 
totalled figures outside the database and then summarised them once a 
minute.


--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] REVOKE ALL

[Richard Huxton]

David Legault wrote:

Hello,

Is there a way to revoke all privileges of a role without actually
specifying the whole list of items.

Like if a role has privileges on FUNCTIONs, is there a REVOKE all 
FUNCTIONS.


There's no GRANT/REVOKE  ON public.* command format, but there are 
plenty of plpgsql functions that do something of the sort.



Is there a way to check if it has a GRANT in a particular type (CONNECT,
FUNCTION, TRIGGER) before calling the REVOKE command?


You can wrap it in a function and check the system catalogues or use the 
has_xxx_privilege() functions, otherwise no.



Also, if I do a GRANT CONNECT ON DATABASE X TO Y, will Y be able to connect
to other databases if I haven't given him permission to do so (what is the
default value when a role is created since roles are global)?


By default all users can connect to all databases. This is limited by 
your pg_hba.conf settings and after that by GRANT CONNECT;


--
  Richard Huxton
  Archonet Ltd

---(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] Installing support for python on windows

[Magnus Hagander]

On Tue, Feb 20, 2007 at 05:15:38PM -0500, Rhys Stewart wrote:
> 
> 
> Ok so i am having trouble installing plpython, and found this thread.
> Howevre, after adding postgresql/bin to the path and the python lib
> directory to the path i still get:
> createlang: language installation failed: ERROR:  could not load library 
> "C:/Pro
> gram Files/PostgreSQL/8.2/lib/plpython.dll": The specified module could not 
> be f
> ound.
> 
> so maybe im doing the path thing wrong? i used  :
> 
> C:\Program Files\PostgreSQL\8.2\bin>set
> PATH=C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;C:\Python24\Lib;c:\Program
> Files\PostggreSQL\8.2\bin;c:\Program Files\PostggreSQL\8.2\lib
> 
> so if that is correct what else am i missing?

Please run the depends.exe tool from the Windows Support Tools to
determine which module it's failing to load.

//Magnus

---(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] Error upgrading on W2K

[Magnus Hagander]

On Wed, Feb 21, 2007 at 12:11:07PM +0900, Paul Lambert wrote:
> I have postgres running on W2K, version 8.2.1 which I am upgrading to 
> 8.2.3 but when I run the upgrade I get an error as follows:
> 
> "The installer has encountered an unexpected error
> installing this package. This may indicate a problem with
> this package. The error code is 2803."
> 
> The install seemed to continue fine after this point and when I check in 
> psql I have 8.2.3 installed and the databases appear to be operational.
> 
> Any thoughts on what the error might have been and if I need to check 
> anything in particular to verify correct install?

Some googling on this (since it's a MSI error and not a pg one) shows
that it has something to do with files in use that cannot be replaced.
Normally it handles the pg files well, but try shutting down the service
before you upgrade. If you have any client services (including IIS)
running that's using the ODBC or libpq drivers, try shutting them down
as well.

Just to make sure you have all the files :)

//Magnus

---(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] Odd behaviour of timestamptz

[Matteo Beccati]

Hi,

Michael Glaesemann wrote:
>> test=# SELECT * from test;
>>data
>> 
>> 1910-01-10 00:00:00+00:19:32
>> 1990-01-10 00:00:00+01
>> (2 rows)
> 
> I suspect your RPMs build PostgreSQL without --enable-integer-datetimes.
> Without this configure flag, timestamps are represented as floats, with
> all of the imprecision that implies. See the second note below the
> Date/Time Types table:
> 
> http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-TABLE

In fact I was thinking to the opposite, but at least one of them didn't
use --enable-integer-datetimes. I could understand problems representing
microseconds, but not the time zone...


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

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

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

Re: [GENERAL] Odd behaviour of timestamptz

[Matteo Beccati]

Matteo Beccati ha scritto:
> Hi,
> 
> I've been recently pointed out an issue with timestamptz on a fedora box
> and no one was able to replicate it on other machines. After a quick
> chat on the IRC at least another two people could replicate the issue
> and all of them were using an RPM package.
> 
> [...]
> and another machine:
> 
> test=# SELECT * from test;
>data
> ---
>  1910-01-10 00:00:00+01:24
>  1990-01-10 00:00:00+01
> (2 rows)

Actually this one was built from source (CVS HEAD from yesterday), so
it's not a packaging issue as I first was thinking.


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

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

Re: [GENERAL] Odd behaviour of timestamptz

[Michael Glaesemann]

On Feb 21, 2007, at 17:15 , Matteo Beccati wrote:


PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-3)

test=# create TABLE test (data timestamp with time zone);
CREATE TABLE

test=# INSERT into test values ('1910-01-10');
INSERT 0 1

test=# INSERT into test values ('1990-01-10');
INSERT 0 1

test=# SELECT * from test;
   data

1910-01-10 00:00:00+00:19:32
1990-01-10 00:00:00+01
(2 rows)


I suspect your RPMs build PostgreSQL without --enable-integer- 
datetimes. Without this configure flag, timestamps are represented as  
floats, with all of the imprecision that implies. See the second note  
below the Date/Time Types table:


http://www.postgresql.org/docs/8.2/interactive/datatype- 
datetime.html#DATATYPE-DATETIME-TABLE


Michael Glaesemann
grzm seespotcode net



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

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

[GENERAL] Odd behaviour of timestamptz

[Matteo Beccati]

Hi,

I've been recently pointed out an issue with timestamptz on a fedora box
and no one was able to replicate it on other machines. After a quick
chat on the IRC at least another two people could replicate the issue
and all of them were using an RPM package.


PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-3)

test=# create TABLE test (data timestamp with time zone);
CREATE TABLE

test=# INSERT into test values ('1910-01-10');
INSERT 0 1

test=# INSERT into test values ('1990-01-10');
INSERT 0 1

test=# SELECT * from test;
   data

1910-01-10 00:00:00+00:19:32
1990-01-10 00:00:00+01
(2 rows)


Similar issues were reported using 8.1.8 on RHEL3:

test=# SELECT * from test2;
   data
---
 1910-01-10 00:00:00+00:09
 1990-01-10 00:00:00+01
(2 rows)


another 8.2.3 on FC3:

test=# SELECT * from test;
 data
--
 1910-01-10 00:00:00+00:09:21
 1990-01-10 00:00:00+01
(2 lignes)


and another machine:

test=# SELECT * from test;
   data
---
 1910-01-10 00:00:00+01:24
 1990-01-10 00:00:00+01
(2 rows)


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

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