[GENERAL] Supported plpgsql BEFORE ... EACH ROW behavior

[Karl O. Pinc]

Hi,

I want to write a plpgsql function for use as a
BEFORE ... EACH ROW function.  I want to modify
other tables even when the function returns NULL
and therefore the table on which the BEFORE
trigger is defined is not updated.

Can I count on this behavior being supported
in the future?  There's nothing in the docs,
or my past experience with Postgresql that
would lead me to believe that the existing
behavior would change, but then the doc's
don't explicitly say what happens to side
effects produced by a BEFORE trigger when
the code returns NULL.

Sorry for being paranoid about this but I
want to double check before relying on
behavior that few people probably use.

Thanks for the help.

Karl <[EMAIL PROTECTED]>
Free Software:  "You don't pay back, you pay forward."
 -- Robert A. Heinlein


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

Re: [GENERAL] PGSQL Locking vs. Oracle's MVCC

[Richard Huxton]

RPK wrote:

How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles
concurreny and how it differs with Oracle's Multi-Version Concurrency
Control (MVCC)?


The manuals are good for this type of thing:
  http://www.postgresql.org/docs/8.2/static/mvcc.html

--
  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] Installing PGSQL Client

[Richard Huxton]

RPK wrote:

I have installed PGSQL on server. How to install PGSQL client to connect to
the PGSQL database server.


You've installed on what? Windows? Solaris? FreeBSD? RedHat Enterprise?...

--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] Large Objects

[Albe Laurenz]

[EMAIL PROTECTED] wrote:
> I'm working on a database that needs to handle insertion of 
> about 10 large objects (50..60GB) a day. It should be 
> able to run 200 days, so it will become about 10TB 
> eventually, mostly of 200..500KB large objects.
> How does access to large objects work ? I give the oid and 
> get the large object... what is done internally ? How (if at 
> all) are the oid's indexed ?

I cannot tell you if PostgreSQL will work well with that amount of data;
I leave this to people more experienced with large databases.
Do you have a backup strategy?

Large objects are stored in a system table pg_largeobject in chunks of
2KB.

The oid is a handle for the large object; when you store the oid in
a user table, you basically store a reference or a pointer to that
large object. If you delete the row in that user table, the large object
will still be there, and if you delete the large object, the oid in the
user table will be 'orphaned'. So you need to make sure that you keep
large objects and references to them in sync.

For your quesion - how are large objects accessed - see the
documentation
of the C functions in
http://www.postgresql.org/docs/current/static/lo-interfaces.html

You can create a new large object, which will return the oid of the new
large object. You have to open a large object by oid before you can read
or modify it - this will give you a 'handle' with which you can access
the contents of the large object much like a file in C: you can position
a 'location pointer' and read or write a number of bytes.
There are functions to import and export a large object from and to the
file system.

There are similar functions for most client interfaces.

Yours,
Laurenz Albe

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

   http://archives.postgresql.org/

Re: [GENERAL] Installing PGSQL Client

[A. Kretschmer]

am  Thu, dem 22.02.2007, um 23:23:42 -0800 mailte RPK folgendes:
> 
> I have installed PGSQL on server. How to install PGSQL client to connect to
> the PGSQL database server.

Depends on the operating system and distribution. For Debian for
instance:

apt-get install postgresql-client-8.1


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

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

[GENERAL] PGSQL Locking vs. Oracle's MVCC

[RPK]

How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles
concurreny and how it differs with Oracle's Multi-Version Concurrency
Control (MVCC)?
-- 
View this message in context: 
http://www.nabble.com/PGSQL-Locking-vs.-Oracle%27s-MVCC-tf3277425.html#a9114584
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] Installing PGSQL Client

[RPK]

I have installed PGSQL on server. How to install PGSQL client to connect to
the PGSQL database server.
-- 
View this message in context: 
http://www.nabble.com/Installing-PGSQL-Client-tf3277417.html#a9114562
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Triggers inherited?

[Richard Huxton]

Bertram Scharpf wrote:

Hi,

it is very inconvenient for me that triggers aren't inherited:


Foreign keys too (which are a special type of trigger of course).


Is this behaviour to be implemented at any point of time in
the future? Could it be advisible to write the patch? Or is
it just too easy to emulate it?


If you have the time and skills, I think many people would be interested 
in a patch to handle this.


You will want to check the todo list, developers website and FAQ (and of 
course the internals section of the manuals) then discuss things on the 
hackers mailing list.

  http://www.postgresql.org/docs/faqs.TODO.html
  http://www.postgresql.org/developer/
  http://www.postgresql.org/docs/faqs.FAQ_DEV.html
  http://developer.postgresql.org/
--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] postgresql vs mysql

[Joshua D. Drake]

John Smith wrote:
> sounds like you aren't happy with one of the products your company
> offers at
> http://www.commandprompt.com/community/plphp/ - "plphp stands for
> procedural
> language php. the language has the php engine at its core and provides php
> scripting support for procedures and functions in postgresql. written by
> command prompt, inc. plphp is open source and licensed under the php
> license
> and the postgresql (bsd) license."
> 
> maybe you should also remove php 5.1.2 from
> http://planetpostgresql.org/-"hosting provided by the postgresql
> company, command prompt, inc".
> 
> our very own http://www.postgresql.org/ also uses php 5.2.0.
> 
> yeah yeah tco blah blah blah. try stopping support for php and watch the
> use
> plummet.


*cough*, you will note that I have already made the argument *for* PHP
if you read my posts.

Joshua D. Drake

-- 

  === 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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Bill Moran]

Alvaro Herrera <[EMAIL PROTECTED]> wrote:
>
> Joshua D. Drake escribió:
> > Andrej Ricnik-Bay wrote:
> > > On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> > >> That depends greatly on what you're doing with it. Generally, as soon
> > >> as you start throwing a multi-user workload at it, MySQL stops
> > >> scaling. http://tweakers.net recently did a study on that.
> > > I think I recall that wikipedia uses MySQL ... they get quite a few
> > > hits, too, I believe.
> > 
> > And outages if you watch :)
> 
> Does this mean that we believe the Wikipedia would not suffer any
> outages if it ran on Postgres?
> 
> How is the Postgres port of the Wikipedia doing this days anyway?  Is it
> in a shape where one would consider it "competitive"?

I installed wikipgdia for the WPLUG wiki:
http://wplug.ece.cmu.edu/wiki/

We haven't had a lick of trouble with it since it went up.  I don't
believe it's experienced any downtime in many months.

-- 
Bill Moran
Collaborative Fusion Inc.

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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[John Smith]

sounds like you aren't happy with one of the products your company offers at
http://www.commandprompt.com/community/plphp/ - "plphp stands for procedural
language php. the language has the php engine at its core and provides php
scripting support for procedures and functions in postgresql. written by
command prompt, inc. plphp is open source and licensed under the php license
and the postgresql (bsd) license."

maybe you should also remove php 5.1.2 from
http://planetpostgresql.org/-"hosting provided by the postgresql
company, command prompt, inc".

our very own http://www.postgresql.org/ also uses php 5.2.0.

yeah yeah tco blah blah blah. try stopping support for php and watch the use
plummet.

and all this crap about php not having a firm design philosophy. hey, it
works well and is very widely adopted. so maybe that's what a good language
design should have- no firm set-in-stone straitjacket philosophy.

somebody else made a point about how jsp is better than php and then went
onto prove the reverse. if you design a language that has such a high tco
that common isps can't use it, it ain't good design- design doesn't work in
isolation.

speaking of obfuscatingly bloated 90s code- there're quite a few jsp
programmers who can't stomach getting kicked around by php, ajax and flash.

to tie this back to postgresql - want to grab the market share from mysql?
fork your strategy - postgresql v oracle and postgresqlite v mysql.
jzs

On 2/22/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote:

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

Re: [GENERAL] postgresql vs mysql

[Joshua D. Drake]

Ben wrote:
> I'm sorry maybe I missed something, but if you don't need NULLs and feel
> they just add extra work, why don't you just declare all your columns to
> be not null and have them default to zero or an empty string?

Stop making sense!

Joshua D. Drake

> 
> On Feb 22, 2007, at 5:11 PM, Glen Parker wrote:
> 
>> Buy the same token, some application have no use whatsoever for the
>> distinction between NULL and ''.  In that case, the distinction just
>> adds work.
>>
>> I would love to see different ways to handle NULL implemented by the
>> server.  For what I do, NULL could always compare equal to zero and
>> ''.  I have no use for NULL in text values.  I do need it for
>> numerics, however it doesn't mean "unknown", it just means "not
>> entered", which is different because I always treat it as zero.
>>
>> I haven't put enough thought into this to make any sort of
>> comprehensive proposal, but it occurs to me that perhaps it could be
>> integrated into the type system.  If I were able to specify, for any
>> given type, a value that should compare equal to NULL ('' for varchar,
>> 0 for int4, for example), that, in combination with NOT NULL
>> constraints, might just do it for me.
>>
>> -Glen
>>
>>> Well, your mileage must vary. The absence of nulls would make my life
>>> difficult.
>>> Just substitute "unknown" for "null" as mentioned above and the various
>>> operations with "null" make sense. For example, take some days and
>>> low-temperatures:
>>> Mon: 30
>>> Tue: 10
>>> Wed: 0
>>> Thu: unknown
>>> Fri: 0
>>> Sat: unknown
>>> Sun: -5
>>> Was the low temperature the same on:
>>> Mon/Tue: no
>>> Wed/Fri: yes
>>> Thu/Fri: unknown
>>> Thu/Sat: unknown <- the always seemingly confusing null=null is null.
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 


-- 

  === 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 2: Don't 'kill -9' the postmaster

Re: [GENERAL] postgresql vs mysql

[Ben]

I'm sorry maybe I missed something, but if you don't need NULLs and  
feel they just add extra work, why don't you just declare all your  
columns to be not null and have them default to zero or an empty string?


On Feb 22, 2007, at 5:11 PM, Glen Parker wrote:

Buy the same token, some application have no use whatsoever for the  
distinction between NULL and ''.  In that case, the distinction  
just adds work.


I would love to see different ways to handle NULL implemented by  
the server.  For what I do, NULL could always compare equal to zero  
and ''.  I have no use for NULL in text values.  I do need it for  
numerics, however it doesn't mean "unknown", it just means "not  
entered", which is different because I always treat it as zero.


I haven't put enough thought into this to make any sort of  
comprehensive proposal, but it occurs to me that perhaps it could  
be integrated into the type system.  If I were able to specify, for  
any given type, a value that should compare equal to NULL ('' for  
varchar, 0 for int4, for example), that, in combination with NOT  
NULL constraints, might just do it for me.


-Glen


Well, your mileage must vary. The absence of nulls would make my life
difficult.
Just substitute "unknown" for "null" as mentioned above and the  
various

operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5
Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.



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

Re: [GENERAL] postgresql vs mysql

[Andrej Ricnik-Bay]

On 2/23/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote:

Andrej Ricnik-Bay wrote:
> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
>> That depends greatly on what you're doing with it. Generally, as soon
>> as you start throwing a multi-user workload at it, MySQL stops
>> scaling. http://tweakers.net recently did a study on that.
> I think I recall that wikipedia uses MySQL ... they get quite a few
> hits, too, I believe.

And outages if you watch :)

Well, there is that ... I didn't throw that in because I think MySQL is
great, on the contrary - we're having some issues with it here at
work (don't ask), but it is being used for large installations, too.
And then of
course there is this
http://www.linuxquestions.org/questions/showthread.php?p=2141344#post2141344
post where this guy (no idea whether he is (or was) what he claimed
to be) rambling on about how MySQL is better than PostgreSQL.



Joshua D. Drake

Cheers,
Andrej

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

Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Joshua D. Drake]

Alvaro Herrera wrote:
> Joshua D. Drake escribió:
>> Alvaro Herrera wrote:
>>> Joshua D. Drake escribió:
 Andrej Ricnik-Bay wrote:
> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
>> That depends greatly on what you're doing with it. Generally, as soon
>> as you start throwing a multi-user workload at it, MySQL stops
>> scaling. http://tweakers.net recently did a study on that.
> I think I recall that wikipedia uses MySQL ... they get quite a few
> hits, too, I believe.
 And outages if you watch :)
>>> Does this mean that we believe the Wikipedia would not suffer any
>>> outages if it ran on Postgres?
>> I believe it would suffer less outage yes.
> 
> And how is SourceForge doing these days, by the way?

Wonderful of course :)

Sincerely,

Joshua D. Drake



-- 

  === 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 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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Alvaro Herrera]

Joshua D. Drake escribió:
> Alvaro Herrera wrote:
> > Joshua D. Drake escribió:
> >> Andrej Ricnik-Bay wrote:
> >>> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
>  That depends greatly on what you're doing with it. Generally, as soon
>  as you start throwing a multi-user workload at it, MySQL stops
>  scaling. http://tweakers.net recently did a study on that.
> >>> I think I recall that wikipedia uses MySQL ... they get quite a few
> >>> hits, too, I believe.
> >> And outages if you watch :)
> > 
> > Does this mean that we believe the Wikipedia would not suffer any
> > outages if it ran on Postgres?
> 
> I believe it would suffer less outage yes.

And how is SourceForge doing these days, by the way?

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

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

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/07 19:04, Mark Walker wrote:
> I'm not sure what you're trying to do but, it appears that you database
> design is incorrect.  What you need is something like
> 
> CREATE TABLE temp_readings
> (
>  _date Date,
>  temperature double,
>  source varchar(20),
> )
> 
> No reading, no record.  Are you suggesting that you would have a weekly
> set of records for each row?

But you still need NULL/UNKNOWN for outer joins.

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

iD8DBQFF3kkVS9HxQb37XmcRAv7yAJ9Py3X/WGt+qe7R8WF1zyFIO38tNQCfZBaN
GCWtuQ48h1dh5eTL5TSv0cA=
=oFIk
-END PGP SIGNATURE-

---(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: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Joshua D. Drake]

Alvaro Herrera wrote:
> Joshua D. Drake escribió:
>> Andrej Ricnik-Bay wrote:
>>> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
 That depends greatly on what you're doing with it. Generally, as soon
 as you start throwing a multi-user workload at it, MySQL stops
 scaling. http://tweakers.net recently did a study on that.
>>> I think I recall that wikipedia uses MySQL ... they get quite a few
>>> hits, too, I believe.
>> And outages if you watch :)
> 
> Does this mean that we believe the Wikipedia would not suffer any
> outages if it ran on Postgres?

I believe it would suffer less outage yes.

> 
> How is the Postgres port of the Wikipedia doing this days anyway?  Is it
> in a shape where one would consider it "competitive"?

I don't know, I believe citizideum or whatever it is called is
PostgreSQL based.

Joshua D. Drake

> 


-- 

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

[Glen Parker]

Buy the same token, some application have no use whatsoever for the 
distinction between NULL and ''.  In that case, the distinction just 
adds work.


I would love to see different ways to handle NULL implemented by the 
server.  For what I do, NULL could always compare equal to zero and ''. 
 I have no use for NULL in text values.  I do need it for numerics, 
however it doesn't mean "unknown", it just means "not entered", which is 
different because I always treat it as zero.


I haven't put enough thought into this to make any sort of comprehensive 
proposal, but it occurs to me that perhaps it could be integrated into 
the type system.  If I were able to specify, for any given type, a value 
that should compare equal to NULL ('' for varchar, 0 for int4, for 
example), that, in combination with NOT NULL constraints, might just do 
it for me.


-Glen


Well, your mileage must vary. The absence of nulls would make my life
difficult.

Just substitute "unknown" for "null" as mentioned above and the various
operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5

Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.





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

Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)

[Alvaro Herrera]

Joshua D. Drake escribió:
> Andrej Ricnik-Bay wrote:
> > On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
> >> That depends greatly on what you're doing with it. Generally, as soon
> >> as you start throwing a multi-user workload at it, MySQL stops
> >> scaling. http://tweakers.net recently did a study on that.
> > I think I recall that wikipedia uses MySQL ... they get quite a few
> > hits, too, I believe.
> 
> And outages if you watch :)

Does this mean that we believe the Wikipedia would not suffer any
outages if it ran on Postgres?

How is the Postgres port of the Wikipedia doing this days anyway?  Is it
in a shape where one would consider it "competitive"?

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

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

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

Re: [GENERAL] postgresql vs mysql

[Mark Walker]

I'm not sure what you're trying to do but, it appears that you database 
design is incorrect.  What you need is something like


CREATE TABLE temp_readings
(
 _date Date,
 temperature double,
 source varchar(20),
)

No reading, no record.  Are you suggesting that you would have a weekly 
set of records for each row?


CREATE TABLE temp_readings
(
 weekstart date,
 sun double,
   mon double,
tues, double
etc
)

Not such a great way to do it.




Well, your mileage must vary. The absence of nulls would make my life
difficult.

Just substitute "unknown" for "null" as mentioned above and the various
operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5

Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.

So what do we do without a null? Does the "helpful" app convert the
unknowns to zero? That's not right. Are we forced to specify a "special"
value like 999 for the unknown data? Then we have to add extra code to
create that value when the value is unknown and more code still to check
for that value when, say, looking for the lowest or average
temperatures. And we're set up for disaster when someone starts
measuring furnace temps instead of outdoor temps.

Look no further than Y2K to see what happened to those apps that gave
special meaning to 12/31/99.

Cheers,
Steve

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

  



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

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

Re: [GENERAL] postgresql vs mysql

[Joshua D. Drake]

Andrej Ricnik-Bay wrote:
> On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
>> That depends greatly on what you're doing with it. Generally, as soon
>> as you start throwing a multi-user workload at it, MySQL stops
>> scaling. http://tweakers.net recently did a study on that.
> I think I recall that wikipedia uses MySQL ... they get quite a few
> hits, too, I believe.

And outages if you watch :)

Joshua D. Drake

> 
> 
> Cheers,
> Andrej
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>   http://archives.postgresql.org/
> 


-- 

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

[Steve Crawford]

Tim Tassonis wrote:
> Chris wrote:
>> 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.
> 
> I do still think it is a bit of an oddity, the concept of the null
> column. From my experience, it creates more problems than it actually
> solves and generally forces you to code more rather than less in order
> to achieve your goals.

Well, your mileage must vary. The absence of nulls would make my life
difficult.

Just substitute "unknown" for "null" as mentioned above and the various
operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5

Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.

So what do we do without a null? Does the "helpful" app convert the
unknowns to zero? That's not right. Are we forced to specify a "special"
value like 999 for the unknown data? Then we have to add extra code to
create that value when the value is unknown and more code still to check
for that value when, say, looking for the lowest or average
temperatures. And we're set up for disaster when someone starts
measuring furnace temps instead of outdoor temps.

Look no further than Y2K to see what happened to those apps that gave
special meaning to 12/31/99.

Cheers,
Steve

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

[Andrej Ricnik-Bay]

On 2/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:

That depends greatly on what you're doing with it. Generally, as soon
as you start throwing a multi-user workload at it, MySQL stops
scaling. http://tweakers.net recently did a study on that.

I think I recall that wikipedia uses MySQL ... they get quite a few
hits, too, I believe.


Cheers,
Andrej

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

  http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Steve Crawford]

Ron Johnson wrote:
> 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?
> 

Not sure what the OP was getting at but of course an empty string is not
the same as a NULL. You have to follow that MySQL forum thread a few
messages to see the real screwup. Upon learning that an empty string is
not the same a a NULL, he adds a constraint:

ALTER TABLE `tbl`
CHANGE `col`
`col` VARCHAR( 3 ) CHECK (`col` <>'')
NOT NULL

Unfortunately for him, this does not work either. The reason is
clarified in the following message:

"Currently MySQL accepts CHECK syntax but does not implement them. You
can enforce such a rule at database level with a trigger, or at
application level as another contributor suggested."

Huh?

Cheers,
Steve

---(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] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Stephan Szabo [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 22 februari 2007 23:13
>To: Joris Dobbelsteen
>Cc: Martijn van Oosterhout; Robert Haas; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Thu, 22 Feb 2007, Joris Dobbelsteen wrote:
>
>> >-Original Message-
>> >From: [EMAIL PROTECTED]
>> >[mailto:[EMAIL PROTECTED] On Behalf Of 
>Martijn van 
>> >Oosterhout
>> >Sent: donderdag 22 februari 2007 18:17
>> >To: Joris Dobbelsteen
>> >Cc: Robert Haas; pgsql-general@postgresql.org
>> >Subject: Re: [GENERAL] complex referential integrity constraints
>> >
>> >On Thu, Feb 22, 2007 at 05:28:35PM +0100, Joris Dobbelsteen wrote:
>> >> Even worse, I don't you can guarentee that this constraint
>> >is enforced
>> >> at all times. That means, not if you are using triggers.
>> >> The only option seems using foreign keys and put in a lot of
>> >redundant
>> >> data.
>> >
>> >Err, foreign keys are implemented using triggers, so this statement 
>> >is self-contradictary.
>>
>> Are you really sure they are executed under the same 
>visibility rules?
>
>IIRC, the ri triggers use calls that you aren't able to get at 
>in triggers written in any of the PLs, but I think you should 
>be able to replicate the feat in a trigger written in C.

Why they never did that? No need or no pratical situations where it went
wrong?

IMHO it should become possible to expose this functionality to the PL
languages or as part of the trigger system? The current system can be
shown to be underpowered to enforce constraints. It seems a bit
cumbersome to have C functions for this purpose.

However I must admit that looking through the postgres code doesn't make
it much clearer to me whats actually going for these kind of tricky
problems...

- Joris

---(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] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 22 februari 2007 23:15
>To: Joris Dobbelsteen
>Cc: Robert Haas; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Thu, Feb 22, 2007 at 06:51:49PM +0100, Joris Dobbelsteen wrote:
>> >Err, foreign keys are implemented using triggers, so this statement 
>> >is self-contradictary.
>> 
>> Are you really sure they are executed under the same 
>visibility rules?
>
>Reasonably. I have no idea what visibility rules would make 
>any difference at all. AIUI a foreign key just takes a shared 
>lock on the referenced row and all the magic of MVCC makes 
>sure the row exists when the transaction completes.

Try this:
(sorry for any typo's in SQL, if they exist)

CREATE TABLE a (val integer NOT NULL PRIMARY KEY);
CREATE TABLE b (val integer NOT NULL, val2 integer NOT NULL, PRIMARY KEY
(val, val2);
-- we will be doing foreign key ourselves
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO a VALUES (1,1);
INSERT INTO a VALUES (2,2);

-- Now two transaction (T1, T2)
T1: BEGIN ISOLATION LEVEL SERIALIZABLE;
T2: BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Lets see what we have got.
T1: SELECT * FROM a;
T1: SELECT * FROM b;
T2: SELECT * FROM a;
T2: SELECT * FROM b;
-- lets insert something...
T2: INSERT INTO a VALUES (2,100);
-- results in a lock being acquired
T2: SELECT 1 FROM a x WHERE val = 2 FOR SHARE ON x; -- this is your
lock
-- Ok, done for now...
T2: COMMIT; -- now the lock is gone
-- This means T1 doesn't see the row, right?
T1: SELECT * FROM b;
-- now lets delete
T1: DELETE FROM a WHERE val = 2;
-- on cascade delete, thus:
T1: DELETE FROM b WHERE val = 2;-- won't see new tuple
(serializable isolation)
T1: COMMIT;

SELECT * FROM b;
val val2
2   100

Sorry, constraint wasn't enforced ;)
It does matter.

Now try it with this:
CREATE TABLE b (val integer NOT NULL, val2 integer NOT NULL, PRIMARY KEY
(val, val2), FOREIGN KEY val REFERENCES a(val) ON UPDATE CASCADE ON
DELETE CASCADE);
That won't inhibit this behaviour, but proberly enforces the constraint
(as one would have expected). I believe T2 will abort as in the manual.

Your statement might be correct, but it doesn't take enough account of
how the visibility rules under MVCC are played. It seems the foreign
keys (as well as primary keys) have there rules applied differently,
they see that row and will cause an abort.

- Joris

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

[GENERAL] Triggers inherited?

[Bertram Scharpf]

Hi,

it is very inconvenient for me that triggers aren't inherited:

  create table watch (
  mod timestamp with time zone default '-infinity' not null
  );
  
  create function update_mod() returns trigger ...
  
  create trigger update_mod before insert or update on watch
  for each row execute procedure update_mod();
  
  create table some  ( ... ) inherits (watch);
  create table other ( ... ) inherits (watch);


Is this behaviour to be implemented at any point of time in
the future? Could it be advisible to write the patch? Or is
it just too easy to emulate it?

Reimplemeting a trigger for each descending table definitely
dosn't satisfy me.

Thanks in advance,

Bertram


-- 
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de

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

Re: [GENERAL] postgresql vs mysql

[Scott Marlowe]

On Thu, 2007-02-22 at 18:48 -0500, Brandon Aiken wrote:
> Digg and Slashdot use MySQL databases, so clearly they *can* be made to
> support a high-load, high-performance, limited-write style web
> application.  
> 
> You might remember a few months back when SlashDot had to turn off
> threaded replies because the schema for the parent-child field was still
> an UNSIGNED INT4 instead of an UNSIGNED INT8, and they reached the
> maximum value of the field (16.7 million).  Obviously, I have no
> knowledge of the server configuration, hardware configuration, or
> schema, but in-the-wild examples of high performance MySQL installations
> are trivial to find (as are PostgreSQL installations such as the .org
> DNS TLD root).

Actually this has been mentioned before, Slashdot is a good example of
how poorly MySQL scales.  Almost every page you view on slashdot is
actually a static page harvested every x minutes by another process
because dynamically generating those pages is very expensive.

If slashdot was able to run on top of MySQL without all that hand
holding, then it would be a showcase for it.

> I'd like to see a tuned MySQL vs a similarly tuned PostgreSQL system
> (that is, fsync in the same state and with the same level of ACID
> compliance) subject to a battery of test schema types (OLTP, OLAP,
> etc.).

Me too.  But I gave up on using MySQL for serious uses some time ago
when I realized that updates for serious bug fixes took years to come
out of the pipeline, and some will simply never be fixed.

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

Re: [GENERAL] postgresql vs mysql

[Brandon Aiken]

Digg and Slashdot use MySQL databases, so clearly they *can* be made to
support a high-load, high-performance, limited-write style web
application.  

You might remember a few months back when SlashDot had to turn off
threaded replies because the schema for the parent-child field was still
an UNSIGNED INT4 instead of an UNSIGNED INT8, and they reached the
maximum value of the field (16.7 million).  Obviously, I have no
knowledge of the server configuration, hardware configuration, or
schema, but in-the-wild examples of high performance MySQL installations
are trivial to find (as are PostgreSQL installations such as the .org
DNS TLD root).

I'd like to see a tuned MySQL vs a similarly tuned PostgreSQL system
(that is, fsync in the same state and with the same level of ACID
compliance) subject to a battery of test schema types (OLTP, OLAP,
etc.).

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: Jim Nasby [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 22, 2007 6:28 PM
To: Brandon Aiken
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql

On Feb 21, 2007, at 2:23 PM, Brandon Aiken wrote:
> IMX, the only things going for MySQL are:
> 1. It's fast.

That depends greatly on what you're doing with it. Generally, as soon  
as you start throwing a multi-user workload at it, MySQL stops  
scaling. http://tweakers.net recently did a study on that.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)





** 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 6: explain analyze is your friend

Re: [GENERAL] postgresql vs mysql

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/07 17:17, Jim Nasby wrote:
> On Feb 21, 2007, at 10:26 AM, Scott Marlowe wrote:
>> 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.
> 
> Reindexing won't re-write a table; clustering will. Also some ALTER
> TABLE commands will (such as changing the data type of a column, or
> creating a new column that's NOT NULL).

Man, that's just Not Right.


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

iD8DBQFF3itiS9HxQb37XmcRAqnWAJ0R9MmdmlUR92B3F81vlGI/D7Es8ACgmiKI
F6BrBA/ZeTsciqJAdiYfTCo=
=KJDL
-END PGP SIGNATURE-

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

Re: [GENERAL] php professional

[Guido Neitzer]

On 22.02.2007, at 16:03, Ted Byers wrote:

One of my problems with database development is how to construct  
analogously strong test cases in order to prove the code correct.


With tests you can't prove that your code is correct. You can only  
show that your code works with the test cases. There are ways of  
proving that code is correct, but I don't know anybody using that on  
"real world code".


If you are using Java as a client you can use DbUnit for unit testing:

http://www.dbunit.org/

This can or cannot show that your latest changes didn't break stuff  
that used to work ... ;-)


cug

---(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] How would you handle updating an item and related stuff all at once?

[Guido Neitzer]

On 22.02.2007, at 15:56, Jim Nasby wrote:

and "surrogate key fields should be named 'id'" (I *much* prefer  
the form "object_id", ie: user_id, used *everywhere*, including the  
user table (in that example)).
Fortunately, with rails extensibility it shouldn't be hard to  
change those default behaviors (in fact there's probably a patch  
somewhere for the first case...)


It's really not hard to use the integrated mechanism for that:

class MyTable < ActiveRecord::Base
set_primary_key "mytable_id"
end

There are nevertheless usage glitches as far as I know. But it's no  
real problem.


cug



---(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] Out of memory on vacuum analyze

[Jim Nasby]

On Feb 21, 2007, at 12:58 AM, Stefan Kaltenbrunner wrote:

Have you actually measured a performance improvment going beyond
250-350MB(that seemed about to be the sweet spot last I tested) or so
for index creation and friends ?


To be honest, no; I just set it high to play on the safe side. But I  
have seen reports of large in-memory sorts actually being slower than  
tape sorts in some cases, so I probably am leaving some performance  
on the table.

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

Re: [GENERAL] postgresql vs mysql

[Jim Nasby]

On Feb 21, 2007, at 2:23 PM, Brandon Aiken wrote:

IMX, the only things going for MySQL are:
1. It's fast.


That depends greatly on what you're doing with it. Generally, as soon  
as you start throwing a multi-user workload at it, MySQL stops  
scaling. http://tweakers.net recently did a study on that.

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(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] php professional

[Mark Walker]

>>>One of my problems with database development is how to construct 
analogously strong test cases in order to prove the code correct. <<<


I have found the best method is to be as random as possible.  I think 
coders subconsciously only test with data they think will work so they 
don't have worry about working late.  They're biased.  We're all so 
programmed to think in deterministic ways that we don't recognize the 
power of pure random guessing.



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

  http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Jim Nasby]

On Feb 21, 2007, at 10:26 AM, Scott Marlowe wrote:

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.


Reindexing won't re-write a table; clustering will. Also some ALTER  
TABLE commands will (such as changing the data type of a column, or  
creating a new column that's NOT NULL).

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

  http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Jim Nasby]

On Feb 20, 2007, at 11:59 PM, Adam Rich wrote:

"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."


Yes, but any session is free to change that setting and insert  
whatever garbage they want. AFAIK there's absolutely no way to  
prevent that. So your data is still very much subject to getting  
trashed.


Of course, there's also plenty of ways to do that on accident, mostly  
involving mistakenly ending up with a MyISAM table instead of an  
InnoDB one.

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

  http://archives.postgresql.org/

Re: [GENERAL] php professional

[Ted Byers]

Mark> Similar issues with Mysql.  It's faster,

But it doesn't matter *how* fast you get the *wrong* answer. :)

I thought one of the first rules of software engineering was "First make it 
right and only then make it fast!"


Granted, most of my experience has more to do with number crunching and 
scientific computing, but for the past 27 years, I always first ensured the 
code was provably correct before worrying about optimization.  And this 
always involved a test suite that applied every possible mathematical test 
with randomly generated test data.  For example, my code for matrix algebra 
problems generally came with test code and driver that would exercise the 
code with tens of millions of random matrices and vectors.  One failure, 
even in millions of test cases, means the code is somehow broken.  I have 
seen, and fixed, such cases before.  One of my problems with database 
development is how to construct analogously strong test cases in order to 
prove the code correct.  And it seems to get harder as the database gets 
larger.  :-(  In other words, I know how to do this with algebra and 
calculus, and C++/Java/FORTRAN programming, but not with sets and SQL 
programming.


Cheers,

Ted 




---(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] Priorities for users or queries?

[Jim Nasby]

The problem with using simple OS priority settings is you leave  
yourself wide open to priority inversion.


There is already work being done on a queuing system; take a look at  
the bizgres archives.


On Feb 20, 2007, at 5:19 PM, Ron Mayer wrote:


Bruce Momjian wrote:

Hard to argue with that.


Is it a strong enough argument to add a TODO?


I'm thinking some sort of TODO might be called for.

Perhaps two TODOs?
  * Use the OS's priority features to prioritize
backends (and document that it might work
better with OS's that support priority inheritance).
  * Investigate if postgresql could develop an
additional priority mechanism instead of using
the OS's.


Ron Mayer wrote:

Magnus Hagander wrote: ...

quite likely to suffer from priority inversion

... CMU paper... tested PostgreSQL (and DB2) on TPC-C
and TPC-W ...found that...I/O scheduling through
CPU priorities is a big win for postgresql.

http://www.cs.cmu.edu/~bianca/icde04.pdf


Setting priorities seems a rather common request,
supposedly coming up every couple months [5].

The paper referenced [1] suggests that even with
naive schedulers, use of CPU priorities is very
effective for CPU and I/O intensive PostgreSQL
workloads.

If someone eventually finds a workload that does suffer
worse performance due to priority inversion,
(a) they could switch to an OS and scheduler
that supports priority inheritance;
(b) it'd be an interesting case for a paper
rebutting the CMU one; and
(c) they don't have to use priorities.

If a user does find he wants priority inheritance it
seems Linux[1], BSD[2], some flavors of Windows[3],
and Solaris[4] all seem to be options; even though
I've only seen PostgreSQL specifically tested for
priority inversion problems with Linux (which did
not find problems but found additional benefit of
using priority inheritance).




[1] Linux with Priority inheritance showing benefits for
PostgreSQL
http://www.cs.cmu.edu/~bianca/icde04.pdf
[2] BSD priority inheritance work mentioned:
http://www.freebsd.org/news/status/report-july-2004-dec-2004.html
[3] Windows priority inheritance stuff:
http://msdn2.microsoft.com/en-us/library/aa915356.aspx
[4] Solaris priority inheritance stuff
http://safari5.bvdep.com/0131482092/ch17lev1sec7
http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/
[5] Tom suggests that priorities are a often requested feature.
http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php

---(end of  
broadcast)---

TIP 3: Have you checked our extensive FAQ?

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



--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

Re: [GENERAL] How would you handle updating an item and related stuff all at once?

[Jim Nasby]

On Feb 20, 2007, at 9:17 AM, Ian Harding wrote:

On 2/17/07, Rick Schumeyer <[EMAIL PROTECTED]> wrote:

I have three tables of interest...Account, Employee, and
AccountEmployeeRelation.  There is a many-to-many relationship  
between

accounts and employees.  The join table also contains a column
indicating what role the employee plays on this account.

My interface is a web app (I'm trying out Ruby on Rails).  On the  
"edit

account" screen I want to edit account attributes AND be able to
add/delete employees in one form.  The gui part seems to work.


Be careful about some of the things Rails tries to push you towards,  
such as "RI belongs in the application", and "surrogate key fields  
should be named 'id'" (I *much* prefer the form "object_id", ie:  
user_id, used *everywhere*, including the user table (in that  
example)). Fortunately, with rails extensibility it shouldn't be hard  
to change those default behaviors (in fact there's probably a patch  
somewhere for the first case...)



BUT, when I update I'm not sure how to handle updating the
AccountEmployeeRelation table.  During the update, relations may have
been added or deleted, and existing relations may have been  
changed.  It
seems to me the easiest thing to do is delete all the relations  
for the
account and create all new ones with the data submitted from the  
form.

This seems wasteful, but the alternative would be a pain.  Or is this
really the best way?



I tried a bunch of cleverness where I checked for existence and
updated if required, etc but came back to just zapping them all and
inserting.  As long as it's done in a transaction and there are not
too many, it's fine.  It doesn't eat any more space and eats less
cycles than doing it the hard way.


Actually, zapping and re-creating everything *does* take more space,  
due to how MVCC works in PostgreSQL. But so long as you're not doing  
that a heck of a lot, it's probably not worth worrying about. It  
might be worth detecting the case where nothing changes, though  
(which I suspect could be done with creative use of INTERSECT and  
it's ilk).



Thanks for any advice.



You're welcome!

Completely off topic, (but not worth a separate post) I have been  
forced

to use a little bit of mysql lately...did you know that if you use
transaction and foreign key syntax with myisam tables, it does not
complain...it just silently ignores your requests for transactions  
and

foreign key checks.  Yikes!  I had incorrectly assumed I would get an
error message indicating that transactions are not supported.  Oh  
well.




Sorry about that.  Nuff said 8^/


That's one gotcha out of about 100. Google 'mysql gotchas' and hit  
the first link.

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

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

[Jim Nasby]

On Feb 21, 2007, at 11:47 PM, sangeetha k.s wrote:

 i want to know
  1.what compression technique used in on disk implementation  
of bitmap index.


Use the source, luke.

You should also take a look at the discussions on the mailing lists  
surrounding it's development. I know early stuff was done on the  
bizgres list, while later stuff is on pgsql-hackers. I'm pretty sure  
there's discussion of the compression in the -hackers archives.


  2.if we want add a new thing to the development how we can  
test that with the database


Huh? I don't know what you're asking here...
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



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

[GENERAL] Large Objects

[haukinger]

Hi all !

I'm working on a database that needs to handle insertion of about 10 large 
objects (50..60GB) a day. It should be able to run 200 days, so it will become 
about 10TB eventually, mostly of 200..500KB large objects.
How does access to large objects work ? I give the oid and get the large 
object... what is done internally ? How (if at all) are the oid's indexed ?

Thanks a lot in advance !

Haukinger
-- 
"Feel free" - 5 GB Mailbox, 50 FreeSMS/Monat ...
Jetzt GMX ProMail testen: www.gmx.net/de/go/mailfooter/promail-out

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

Re: [GENERAL] complex referential integrity constraints

[Robert Haas]

Yes, exactly.  And while you might not care about all of those (e.g. I
care about the first two but am not worried about the third one because
I'm the only one who will ever update that table), writing multiple
triggers to enforce each constraint of this type quickly gets old if
there are even a few of them.  It is exponentially harder to write a
constraint of this type than it is to write a simple foreign key
constraint.

...Robert 

-Original Message-
From: Joris Dobbelsteen [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 19, 2007 5:59 AM
To: elein; Robert Haas
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] complex referential integrity constraints

>Why don't you add a field in animal_types that is boolean mauler.
>Then you can add a trigger on the mauling table to raise an 
>error when the attacker_id is an animal type mauler.

This is only partial. You need a lot more triggers to guarentee the
constraints are enforced.
Precisely you need to validate:
* mauling on insert/update of attacker_id
* animal on update of type_id
* animal_type on update of your property

Of course you need to think about the MVCC model, such that:
Transaction 1 executes
INSERT INTO mauling VALUES ('someattacker'),
Transaction 2 executes
UPDATE animal_type SET mauler = false WHERE name = 'someattacker',
such that both transaction happen in parallel.

This is perfectly possible and will make it possible to violate the
constraint, UNLESS locking of the tuples is done correctly.

These contraints are not trivial to implement (unfortunally). It would
be great if they where.

- Joris

---(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] complex referential integrity constraints

[Robert Haas]

The idea here is that a wolf can attack a sheep, or a wolf can attack
another wolf, but sheep can't attack anything.  I suppose I could list
each wolf in both the predator and prey tables, but that seems a bit
duplicative (and causes other problems).

...Robert

-Original Message-
From: David Fetter [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 19, 2007 1:04 PM
To: Robert Haas
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] complex referential integrity constraints

On Fri, Feb 16, 2007 at 09:58:56AM -0500, Robert Haas wrote:
> So, I have the following problem.
> 
> Suppose you have two kinds of animals, sheep and wolves.  Since they
> have very similar properties, you create a single table to hold both
> kinds of animals, and an animal_type table to specify the type of each
> animal:
> 
> CREATE TABLE animal_type (
> idinteger not null,
> name  varchar(80) not null,
> primary key (id)
> );
> INSERT INTO animal_type VALUES (1, 'Sheep');
> INSERT INTO animal_type VALUES (2, 'Wolf');
> 
> CREATE TABLE animal (
> idserial,
> type_id integer not null references animal_type (id), 
> name  varchar(80) not null,
> age   integer not null,
> weight_in_pounds  integer not null,
> primary key (id)
> );
> 
> The animal_type table is more or less written in stone, but the animal
> table will be updated frequently.  Now, let's suppose that we want to
> keep track of all of the cases where one animal is mauled by another
> animal:
> 
> CREATE TABLE mauling (
> id  serial,
> attacker_id integer not null references animal (id),
> victim_id   integer not null references animal (id),
> attack_time timestamp not null,
> primary key (id)
> );
> 
> The problem with this is that I have a very unsettled feeling about
the
> foreign key constraints on this table.  The victim_id constraint is
> fine, but the attacker_id constraint is really inadequate, because the
> attacker CAN NEVER BE A SHEEP.  I really want a way to write a
> constraint that says that the attacker must be an animal, but
> specifically, a wolf.
> 
> It would be really nice to be able to write:
> 
> FOREIGN KEY (attacker_id, 2) REFERENCES animal (id, type_id)
> 
> Or:
> 
> CREATE UNIQUE INDEX wolves ON animal (id) WHERE type_id = 2;
> -- and then
> FOREIGN KEY (attacker_id) REFERENCES INDEX wolves
> 
> ...but that's entirely speculative syntax.  I don't think there's any
> easy way to do this.  (Please tell me I'm wrong.)
> 
> The problem really comes in when people start modifying the animal
> table.  Every once in a while we have a case where we record something
> as a wolf, but it turns out to have been a sheep in wolf's clothing.
In
> this case, we want to do something like this:
> 
> UPDATE animal SET type_id = 1 WHERE id = 572;
> 
> HOWEVER, this operation MUST NOT be allowed if it turns out there is a
> row in the mauling table where attacker_id = 572, because that would
> violate my integrity constraints that says that sheep do not maul.
> 
> Any suggestions?  I've thought about creating rules or triggers to
check
> the conditions, but I'm scared that this could either (a) get really
> complicated when there are a lot more tables and constraints involved
or
> (b) introduce race conditions.
> 
> Thanks,
> 
> ...Robert

I'd do something like this:

CREATE TABLE animal_type (
animal_name  TEXT PRIMARY KEY,
CHECK(animal_name = trim(animal_name))
);

/* Only one of {Wolf,wolf} can be in the table. */

CREATE UNIQUE INDEX just_one_animal_name
ON animal_type(LOWER(animal_name));

CREATE TABLE predator (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);

CREATE TABLE prey (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);

CREATE TABLE mauling (
id SERIAL PRIMARY KEY,
attacker_idINTEGER NOT NULL REFERENCES predator
(animal_type_id),
victim_id  INTEGER NOT NULL REFERENCES prey (animal_type_id),
attack_timeTIMESTAMP WITH TIME ZONE NOT NULL
);

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

Remember to vote!

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

[Alexander Elgert]

Hello,

thank you for the information, but it seems my messages are hold for 
moderator approval.

A few of them seems to be dropped - I don't know.

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 triggers, please do not blame me for the structure, it 
was not mine:


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 6: explain analyze is your friend

Re: [GENERAL] complex referential integrity constraints

[Robert Haas]

The ability to make a foreign key reference a specific partial unique
index (rather than just a set of columns that have a unique index) would
solve many problems of this type.  As another example, you might have a
table where one of the columns is "is_deleted boolean not null".  By
creating a partial unique index on the primary key of that table "WHERE
NOT is_deleted" and then pointing a foreign key at it, you could enforce
that each row in the child table references a parent who isn't deleted.

However, this would break down when there's more one than intermediate
step involved.  For example, if you have:

CREATE TABLE animal_type (
  idserial,
namevarchar(60) not null,
is_attacker boolean not null,
primary key (id)
);

CREATE TABLE animal (
id  serial,
type_id integer not null references animal_type (id),
namevarchar(60) not null,
primary key (id)
);

CREATE TABLE mauling (
id  serial,
attacker_id integer not null references animal (id),
victim_id   integer not null references animal (id),
attack_time timestamp with time zone not null,
primary key (id)
);

It would be easy to enforce the constraint that the attacker must be an
animal of some specific type, but difficult to enforce the constraint
that the attacker must be an animal whose type, in turn, has a true
value for is_attacker.

The best idea that I can think of right now to handle multiple levels of
tables is to allow FOREIGN KEY constraints to references a VIEW, rather
than a table.  Then you could say:

CREATE VIEW attackers AS
SELECT a.id FROM animal a, animal_type t WHERE a.type_id = t.id AND
t.attacker;

...and then FOREIGN KEY (attacker_id) REFERENCES attackers (id).

This syntax would solve a number of other problems as well, such as
requiring that some record in table A has a parent either in table P or
in table Q.  However, I think this would probably require implementing
some kind of materialized view so that you could actually build an index
on the view, and that opens up a whole new can of worms, because it's
not very difficult to define a view that is costly to update
incrementally.

The problem is really that there is a pretty large gap between writing a
foreign key constraint, which is trivial, and enforcing a constraint
using triggers, which is quite a bit more complex (and therefore, easy
to screw up), because the foreign key automatically handles all the
cases (insert into child table, update of child table, update of parent
table, delete from parent table) whereas with triggers you have to
address each of those cases individually.  Unfortunately, something
tells me that implementing a more powerful system for foreign key
constraints is a non-trivial project, however useful it would be.
Still, I'd love to see it in the TODO file, too.

...Robert

-Original Message-
From: Joris Dobbelsteen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 22, 2007 8:03 AM
To: Robert Haas; elein
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] complex referential integrity constraints

I partially agree:
If people CAN do stupid things, they are 'clever' enough to find a way
to actually do it. I've seen them destroy things, by just using a system
in a way it was not intended. They effectively found a way to blow away
the very thing that part was designed for.
But indeed, it's a lot of work, especially if the number of tables that
must be referenced increases. I'm a strong supporter for ensuring
consistency. Postgres has what it takes to do the job, but it doesn't
make my life a lot easier. But it seems to be as good as it gets
today...

Perhaps we should rather define a 'database' constraint in the order of:
"For every mauling, the attacking animal must be of the attacker type"
(in a computer understandable manner). From the set theory this should
be possible without too much problems, However doing so efficiently
might be slightly harder.
This might be a fun project and useful for the TODO list. At least it
makes it a lot easier (and maintanable) to enforce database-wide
constraints.

- Joris

>-Original Message-
>From: Robert Haas [mailto:[EMAIL PROTECTED] 
>Sent: woensdag 21 februari 2007 3:37
>To: Joris Dobbelsteen; elein
>Cc: pgsql-general@postgresql.org
>Subject: RE: [GENERAL] complex referential integrity constraints
>
>Yes, exactly.  And while you might not care about all of those 
>(e.g. I care about the first two but am not worried about the 
>third one because I'm the only one who will ever update that 
>table), writing multiple triggers to enforce each constraint 
>of this type quickly gets old if there are even a few of them. 
> It is exponentially harder to write a constraint of this type 
>than it is to write a simple foreign key constraint.
>
>...Rober

Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[Shashank Tripathi]

 I would do a CHECK (trim(a) <> '')



TRIM() would add some processing time, so I'd include it only if there
was a chance of spaces getting added. From a puritanical point of
view, it is definitely a good idea.

To the original poster, this syntax should work in MySQL as well:

  create table mytable (mycol text not null check (mycol <> ''));

Problem is, if you created your table before MySQL 5, and now simply
want to ALTER your table (which is what I gather you wish to do, as
you already have the table) then adding the CHECK condition may not
work.

I cannot help in this case, and from the turn this thread has taken,
not many others I suppose. Why not try a MySQL experts list instead of
PostgreSQL, but be prepared to have to recreate the table in MySQL 5
with the CHECK constraint, and then importing your data in to it.

Good luck!

---(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] Warning "TupleDesc reference leak"

[Marek Lewczuk]

Hello,
after upgrade to 8.2 version, PostgreSQL throws following warnings:
WARNING:  TupleDesc reference leak: TupleDesc 0x42051d90 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x41f60ad0 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x4203d908 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x41fdc410 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x41fbb568 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x42044bf0 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x42038e60 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x41feebc0 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x41fa0018 (16425,-1) 
still referenced
WARNING:  TupleDesc reference leak: TupleDesc 0x41fd9c30 (16425,-1) 
still referenced


What it means ?

Thanks

ML



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

Re: [GENERAL] Synchronize tables question....

[Michael Raven]

Jerry LeVan wrote:
> 
> 
> Is there an elegant way I can merge/update the two tables so that
> they will contain the same information ( with no duplicates or  
> omissions)?
> 

Well comparing algorithm is quite complex and depend on data.
You can use third patry software like
http://www.sqlmanager.net/en/products/postgresql/datacomparer
-- 
View this message in context: 
http://www.nabble.com/Synchronize-tables-question-tf3242977.html#a9055565
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql

[Shashank Tripathi]

On 22/02/07, Shashank Tripathi <[EMAIL PROTECTED]> wrote:

>  I would do a CHECK (trim(a) <> '')


TRIM() would add some processing time, so I'd include it only if there
was a chance of spaces getting added. From a puritanical point of
view, it is definitely a good idea.

To the original poster, this syntax should work in MySQL as well:

   create table mytable (mycol text not null check (mycol <> ''));

Problem is, if you created your table before MySQL 5, and now simply
want to ALTER your table (which is what I gather you wish to do, as
you already have the table) then adding the CHECK condition may not
work.

I cannot help in this case, and from the turn this thread has taken,
not many others I suppose. Why not try a MySQL experts list instead of
PostgreSQL, but be prepared to have to recreate the table in MySQL 5
with the CHECK constraint, and then importing your data in to it.

Good luck!




Sorry, I spoke too soon. MySQL does not do the constraints jig yet.

"The CHECK  clause is parsed but ignored by all storage engines."
- From http://dev.mysql.com/doc/refman/5.0/en/create-table.html

So you may want to adopt some kludges, such as updatable views:
http://arjen-lentz.livejournal.com/49881.html

If I were you, I'd just stick to error-checking in the application
layer for now, or consider slowly switching to PostgreSQL. (No plug
intended)

Shanx

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

Re: [GENERAL] postgresql vs mysql

[Mark Walker]

If you don't know something, why are you trying to record it?  From a strict 
relational sense, the existence of NULL values in your fields indicates that 
your primary keys are not

truly candidate keys for all your fields.  That means your database isn't [BCNF] 
normalized.<<<

I agree that there are very few times when NULL is appropriate in a database.  I can't think of a single concrete example to use it in a database field. 


It has its use in programming, mainly as a memory management/trash collection 
mechanism.  Basically, you don't want to delete something that doesn't exist.

For example:

Statement *st = NULL;
ResultSet *rs = NULL;
try
{
st = prepareStatement("select * from customers");
rs = st->executeQuery();
while (rs->next())
{
do something
}
delete st;
delete rs;
}
catch (Exception e)
{
if (st != NULL)
   delete st;
if (rs != NULL)
   delete rs;
}



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

Re: [GENERAL] complex referential integrity constraints

[Martijn van Oosterhout]

On Thu, Feb 22, 2007 at 06:51:49PM +0100, Joris Dobbelsteen wrote:
> >Err, foreign keys are implemented using triggers, so this 
> >statement is self-contradictary.
> 
> Are you really sure they are executed under the same visibility rules?

Reasonably. I have no idea what visibility rules would make any
difference at all. AIUI a foreign key just takes a shared lock on the
referenced row and all the magic of MVCC makes sure the row exists when
the transaction completes.

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] complex referential integrity constraints

[Stephan Szabo]

On Thu, 22 Feb 2007, Joris Dobbelsteen wrote:

> >-Original Message-
> >From: [EMAIL PROTECTED]
> >[mailto:[EMAIL PROTECTED] On Behalf Of
> >Martijn van Oosterhout
> >Sent: donderdag 22 februari 2007 18:17
> >To: Joris Dobbelsteen
> >Cc: Robert Haas; pgsql-general@postgresql.org
> >Subject: Re: [GENERAL] complex referential integrity constraints
> >
> >On Thu, Feb 22, 2007 at 05:28:35PM +0100, Joris Dobbelsteen wrote:
> >> Even worse, I don't you can guarentee that this constraint
> >is enforced
> >> at all times. That means, not if you are using triggers.
> >> The only option seems using foreign keys and put in a lot of
> >redundant
> >> data.
> >
> >Err, foreign keys are implemented using triggers, so this
> >statement is self-contradictary.
>
> Are you really sure they are executed under the same visibility rules?

IIRC, the ri triggers use calls that you aren't able to get at in
triggers written in any of the PLs, but I think you should be able to
replicate the feat in a trigger written in C.

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

[Brandon Aiken]

If you can remove NULLs without breaking OUTER JOIN, more power to you.

In the vast majority of cases, all fields in a table should have a NOT
NULL constraint.  Storing a NULL value makes little sense, since you're
storing something you don't know.  If you don't know something, why are
you trying to record it?  From a strict relational sense, the existence
of NULL values in your fields indicates that your primary keys are not
truly candidate keys for all your fields.  That means your database
isn't [BCNF] normalized.

Arguments about de-normalization generally result in the basic
limitation in nearly all RDBMS's that they do not allow you to optimize
how data is physically stored on disk.  That is, a generalized SQL
database like Oracle, MySQL, PostgreSQL, etc. sacrifice the ability to
control how data is physically store in order to be a generalized
database that can store generic domains in the form of the most common
datatypes that computer programs use.  

This is a basic limitation of using a generalized database engine, and
if your application demands higher performance than you can get with a
general RDBMS, you'll have to develop your own task-specific RDBMS or
modify your schema so that the problem can be mitigated.  Schema
de-normalization is a way of purposefully degrading the normal quality
of your schema in order to make up for shortcomings of the database
engine and limitations of computerized data storage.  As long as you
understand that de-normalization is a practical workaround and never a
wise logical design choice from the get-go, you shouldn't feel too bad
about doing it.

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tim Tassonis
Sent: Thursday, February 22, 2007 10:31 AM
To: Rich Shepard
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql

Rich Shepard wrote:
> On Thu, 22 Feb 2007, Tim Tassonis wrote:
> 
>> I do still think it is a bit of an oddity, the concept of the null 
>> column.
>> From my experience, it creates more problems than it actually solves
and
>> generally forces you to code more rather than less in order to
achieve
>> your goals.
> 
> Tim,
> 
>   Long ago, a lot of database applications used 99, or 999, or -1 to
> indicate an unknown value. However, those don't fit well with a
textual
> field and they will certainly skew results if used in arithmetic
> calculations in numeric fields.

I remember, my first database to write stuff for was an IMB IMS 
hierarchical/network one.

> 
>   The concept of NULL representing an unknown value, and therefore one
that
> cannot be compared with any other value including other NULLs, is no
> different from the concept of zero which was not in mathematics for
the
> longest time until some insightful Arab mathematician saw the need for
a
> representation of 'nothing' in arithmetic and higher mathematics.
> 
>   There was probably resistance to that idea, too, as folks tried to
wrap
> their minds around the idea that 'nothing' could be validly
represented 
> by a
> symbol and it was actually necessary to advance beyond what the Greeks
and
> Romans -- and everyone else -- could do. Now, one would be thought a
bit
> strange to question the validity of zero.

That's one point for me, then!. NULL exactly is _not_ the equivalent the

the number 0, but the mentioned strange symbol that has to be treated 
specially and does not allow normal calculation, like '0' does in 
mathematics. I don't know how many times I had to write a query that 
ends with:

- or column is null
- and column is not null

exactly because it is a special symbol. In mathematics, the only special

case for zero that springs to my mind is the division of something by 
zero (I'm by no means a mathematician).

As a completely irrelevant sidenote to the discussion, I'm greek and not

arabic, but I certinly do accept the superiority of the arabic notation.

> 
>   NULL solves as many intransigent problems with digital data storage
and
> manipulation in databases as zero did in the realm of counting.

As I said, I don't deny it solves some problems (that could be solved in

a different way, too), but in my opinion, it creates more (that also can

  be solved, as above examples show).

Tim


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

   http://archives.postgresql.org/



** 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, distri

Re: [GENERAL] php professional

[Randal L. Schwartz]

> "Mark" == Mark Walker <[EMAIL PROTECTED]> writes:

Mark> Similar issues with Mysql.  It's faster,

But it doesn't matter *how* fast you get the *wrong* answer. :)

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
 http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

---(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] php professional

[Mark Walker]

You're probably right.  A good example of that is the difference between 
the excellent pgadmin and the desktop mysql administrator which is very 
buggy and strangely laid out.  Whenever I have to deal with mysql I get 
the feeling I'm messing around with a bunch of hacks.  It's very strange 
to deal with.  Simple things like just granting access to a database you 
have to issue perhaps 3 different commands some from the os command 
line, some from the db interperter.  I think people who understand the 
importance of administrative simplicity probably also make good coders 
and vice versa.


Scott Marlowe wrote:

I wasn't referring to projects written in both languages.  I was
referring to projects written primarily for MySQL or "real" databases
(i.e. oracle, pgsql, mssql, db2, and on and on).  No matter what
language is used, I think you'll find that apps written primarily for
mysql have poorer code than the ones written primarily for other apps. 
The ones that are truly transportable will generally be the best, but if

they try to support MySQL AND the other real databases, you'll likely
find lots of hacks in the code to keep MySQL happy, that wouldn't be
needed if they didn't support it.

Does that make more sense?

  



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

Re: [GENERAL] php professional

[Scott Marlowe]

I wasn't referring to projects written in both languages.  I was
referring to projects written primarily for MySQL or "real" databases
(i.e. oracle, pgsql, mssql, db2, and on and on).  No matter what
language is used, I think you'll find that apps written primarily for
mysql have poorer code than the ones written primarily for other apps. 
The ones that are truly transportable will generally be the best, but if
they try to support MySQL AND the other real databases, you'll likely
find lots of hacks in the code to keep MySQL happy, that wouldn't be
needed if they didn't support it.

Does that make more sense?

On Thu, 2007-02-22 at 13:36, Mark Walker wrote:
> Hmm, I've never heard of an application that's written in both php and 
> Java.  However, I know of many applications that run on both mysql and 
> postgresql.  For instance phpbb which is the most common MB software is 
> written in php and runs with either postgresql or mysql.  Database 
> server independence is definitely a goal for most non custom applications. 
> 
> Scott Marlowe wrote:
> > On Thu, 2007-02-22 at 12:17, Mark Walker wrote:
> >
> > While I'll admit to some similarities between PHP/java and mysql/pgsql,
> > I'd say that th gulf between php and java is far less than the gulf is
> > between mysql and pgsql.
> >
> > Take a list of a hundred or so db based projects written in each
> > language.  Correlate it to the db used.  It's likely you'll find a
> > better correlation to the db used than the language.
> >
> >
> >   
> 
> 
> ---(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

---(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] php professional

[Mark Walker]

Hmm, I've never heard of an application that's written in both php and 
Java.  However, I know of many applications that run on both mysql and 
postgresql.  For instance phpbb which is the most common MB software is 
written in php and runs with either postgresql or mysql.  Database 
server independence is definitely a goal for most non custom applications. 


Scott Marlowe wrote:

On Thu, 2007-02-22 at 12:17, Mark Walker wrote:

While I'll admit to some similarities between PHP/java and mysql/pgsql,
I'd say that th gulf between php and java is far less than the gulf is
between mysql and pgsql.

Take a list of a hundred or so db based projects written in each
language.  Correlate it to the db used.  It's likely you'll find a
better correlation to the db used than the language.


  



---(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] Recursive Left Joins Causing Trouble in 8.2.3 RESOLVED (kind of)

[Ian Harding]

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

"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



True.  Yesterday was my last day at that employer, today is my first
at my new one.  I needed to get things going and was just happy to be
able to turn enable_nestloop back on and have things work.

I will see if I can get the problem recreated without too much
extraneous junk, it was buried pretty deep in views referencing views
referencing views which took what may be a minor change in query
timing and planning complexity and blowing it out.  The odd query
structure (I thought) was a series of self left-joins but that may
have been smoke.

I was just surprised that basically my entire system came to a halt
with a minor version upgrade, and that nobody else had seen anything
similar.  I know I write careless SQL from time to time, but again, I
didn't think I was THAT unique!

- Ian

---(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] php professional

[Scott Marlowe]

On Thu, 2007-02-22 at 12:17, Mark Walker wrote:

While I'll admit to some similarities between PHP/java and mysql/pgsql,
I'd say that th gulf between php and java is far less than the gulf is
between mysql and pgsql.

Take a list of a hundred or so db based projects written in each
language.  Correlate it to the db used.  It's likely you'll find a
better correlation to the db used than the language.



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

Re: [GENERAL] Moving WAL files

[Tomas Simonaitis]

> Quick question, you mentioned LVM snapshots, and I am not aware what
> it does! Is there any doc that explains it? Can you point me to some
> such resources?

Newer LVM versions support read/write snapshots [essentially cheap clone of 
all partition data].
http://tldp.org/HOWTO/LVM-HOWTO/snapshots_backup.html is good document about 
them.

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

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

Re: [GENERAL] Moving WAL files

[Dhaval Shah]

Tomas,

I plan to do something similar [except snapshots] so if I run into
issues, I will let you know.

Quick question, you mentioned LVM snapshots, and I am not aware what
it does! Is there any doc that explains it? Can you point me to some
such resources?

Regards
Dhaval

On 2/22/07, Tomas Simonaitis <[EMAIL PROTECTED]> wrote:

Hi,

I've got following online-backup setup (v. 8.1.8):
- on master
-- archive_command = 'mv %p //%f
-- rsyncd with access to DB data and 

- on slave
-- rsync client running every 10sec. to sync  and 
directories to slave:
rsync -a --delete master:: 
rsync -a --delete master:: 

-- pg_start_backup via ssh/rsync DB dir/pg_stop_backup via ssh/old wals
cleanup every hour:
ssh master --command "\"SELECT pg_start_backup('${BKNAME}');\""
rsync -a --exclude=pg_xlog/ --delete main:: 
ssh master psql --command "\"SELECT pg_stop_backup();\""

-- sql dump every 2 hours:
create LVM snapshot from  partition, start postgresql server
with pgdata set to snapshot mountpoint, pg_dumpall, stop, remove snapshot

Setup works perfectly fine, but I' not sure about several things:
1. Is it ok to move instead of copy in archive_command? I use move since
 is on same partition as .
2. I just rsync pg_xlog directories instead of hunting down active WAL file
and copying it. Any problems possible because of that?
3. Should I worry about using only "rsync -a" (i.e. checking only mtime,
instead of checksum to see if files differ).
4. during "old wals cleanup" I remove all wal files upto last WAL.backup
(excluding that), afaik thats always ok?

Thanks.

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




--
Dhaval Shah

---(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] php professional

[Lincoln Yeoh]

At 02:16 AM 2/23/2007, Joshua D. Drake wrote:

We do not compete with MySQL.

Does MySQL have the mindshare of the ignorant? Yes.
Does MySQL have the mindhare of the knowledgeable? No.

Our mindshare is *huge* with the knowledgeable.

I will take mindshare with the knowledgeable over the ignorant, every
second of the day. They pay better.


The ignorant outnumber the knowledgeable by magnitudes. Lots of 
ignorant people paying small sums of money add up to quite a lot 
(politicians exploit that regularly).


Also those expensive consultants find it much easier to get lots of 
money from ignorant people on a regular basis :).


But if you're the sort who actually cares, it's easier to sleep at 
night the postgresql way ;).


Regards,
Link.


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

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

Re: [GENERAL] php professional

[Mark Walker]

I think a lot of the reasons people use LAMP is that inexpensive ISPs 
use LAMP.  The reasons ISPs use LAMP as opposed to other, in my opinion 
more powerful tools has to do with the complexities of hosting large 
numbers of user applications on single machines. 

For instance, I don't know anybody who's ever coded in both PHP and 
Java/Servlet/jsp who would choose PHP.  You'd have to be an idiot to 
choose PHP over Java/jsp.  However, PHP is designed to very quickly 
induce an application state, run the application, and then archive the 
application state out of processor memory.  Servlets are not designed to 
do that.  They are designed to run on a Java Virtual Machine that 
remains resident indefinitely.  As an ISP you have the choice to let all 
your users share the same application state with Java, very insecure, or 
run multiple jvms, very memory intensive.  PHP works much better for you 
because apps induce their state, do what they need to do for each 
request, and then disappear.


Similar issues with Mysql.  It's faster, yet less powerful for 
transaction processing and data integrity than Postgresql.  Well, most 
people who own their own servers are going be more worried about data 
integrity and transaction processing than pure speed.  An ISP, however, 
wants speed less processor usage.  The other thing is that Mysql has a 
faster connection/disconnection cycle.  You can use connection pooling 
with Postgresql which is very fast, but again, more memory and 
application cycle problems.


Given the choice, I think Postgresql is easier to work with, more 
robust, and easier to maintain than Mysql.  However, if you want a $10 a 
month shared hosting account, you're going to almost definitely be 
forced to use Mysql.  Issues of speed?  I don't know, but I haven't 
reached any speed bottlenecks with Postgresql.  Internet connection 
speeds and other application speed issues are usually the bottlenecks.  
It may just be what I do, but I'm definitely more worried about the 
integrety/security of my data than I am about speed.




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

Re: [GENERAL] php professional

[Joshua D. Drake]

>> O.k. this is bizarre. One, this discussion belongs on -advocacy not
>> -general.
>>
>> Two, you do realize that we have huge mind share right?
> 
> Huge? Nah. AFAIK, Oracle hasn't tried to buy up the major suppliers[1]
> of postgresql's "backend" tech yet.

Ahh, because they can't? Oracle can't stop PostgreSQL because PostgreSQL
is BSD licensed.

Sure Oracle could purchase CMD (or more likely EDB just to remove a
small thorn) but it wouldn't do anything the PostgreSQL machine.

You are mistaking pretty pictures of non-reality with hard core truth.
They are not the same.

I have never lost a contract to MySQL. I have lost contracts to Oracle.

We do not compete with MySQL.

Does MySQL have the mindshare of the ignorant? Yes.
Does MySQL have the mindhare of the knowledgeable? No.

Our mindshare is *huge* with the knowledgeable.

I will take mindshare with the knowledgeable over the ignorant, every
second of the day. They pay better.

Joshua D. Drake


> 
> Link.
> 
> [1] e.g. Tom Lane.
> 
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 


-- 

  === 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 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] php professional

[Alvaro Herrera]

Lincoln Yeoh wrote:
> At 01:30 AM 2/23/2007, Joshua D. Drake wrote:
> 
> >Two, you do realize that we have huge mind share right?
> 
> Huge? Nah. AFAIK, Oracle hasn't tried to buy up the major 
> suppliers[1] of postgresql's "backend" tech yet.
> 
> Link.
> 
> [1] e.g. Tom Lane.

How do you know?  I'm pretty sure if Tom wanted to work for Oracle he
would have sent a resume already :-)

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

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

Re: [GENERAL] php professional

[Lincoln Yeoh]

At 01:30 AM 2/23/2007, Joshua D. Drake wrote:


>> Answer for this is a bit complex, more newbies howtos, more people
>> saying that is better and so on
>
> Yeah. Would be good if we can figure out something that would help
> postgresql increase its usage or mind share.


O.k. this is bizarre. One, this discussion belongs on -advocacy not
-general.

Two, you do realize that we have huge mind share right?


Huge? Nah. AFAIK, Oracle hasn't tried to buy up the major 
suppliers[1] of postgresql's "backend" tech yet.


Link.

[1] e.g. Tom Lane.


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

[GENERAL] Moving WAL files

[Tomas Simonaitis]

Hi,

I've got following online-backup setup (v. 8.1.8):
- on master
-- archive_command = 'mv %p //%f
-- rsyncd with access to DB data and 

- on slave
-- rsync client running every 10sec. to sync  and  
directories to slave:
rsync -a --delete master:: 
rsync -a --delete master:: 

-- pg_start_backup via ssh/rsync DB dir/pg_stop_backup via ssh/old wals 
cleanup every hour:
ssh master --command "\"SELECT pg_start_backup('${BKNAME}');\""
rsync -a --exclude=pg_xlog/ --delete main:: 
ssh master psql --command "\"SELECT pg_stop_backup();\""

-- sql dump every 2 hours:
create LVM snapshot from  partition, start postgresql server 
with pgdata set to snapshot mountpoint, pg_dumpall, stop, remove snapshot

Setup works perfectly fine, but I' not sure about several things:
1. Is it ok to move instead of copy in archive_command? I use move since 
 is on same partition as .
2. I just rsync pg_xlog directories instead of hunting down active WAL file 
and copying it. Any problems possible because of that?
3. Should I worry about using only "rsync -a" (i.e. checking only mtime, 
instead of checksum to see if files differ).
4. during "old wals cleanup" I remove all wal files upto last WAL.backup 
(excluding that), afaik thats always ok?

Thanks.

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

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>Martijn van Oosterhout
>Sent: donderdag 22 februari 2007 18:17
>To: Joris Dobbelsteen
>Cc: Robert Haas; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Thu, Feb 22, 2007 at 05:28:35PM +0100, Joris Dobbelsteen wrote:
>> Even worse, I don't you can guarentee that this constraint 
>is enforced 
>> at all times. That means, not if you are using triggers.
>> The only option seems using foreign keys and put in a lot of 
>redundant 
>> data.
>
>Err, foreign keys are implemented using triggers, so this 
>statement is self-contradictary.

Are you really sure they are executed under the same visibility rules?

- Joris

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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Chad Wagner]

On 2/22/07, Martijn van Oosterhout  wrote:


On Thu, Feb 22, 2007 at 12:05:20PM +1100, Chris wrote:
> >SELECT foo, bar, COUNT(*)
> >FROM baz
> >GROUP BY foo

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

I must say I don't see any reasonable way of interpreting the above
query. Is the value of bar selected randomly?



The value of bar happens to be the first value fetched based on the GROUP BY
of foo, not sure how predictable and repeatable it is.

Re: [GENERAL] php professional

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/07 10:40, Joshua D. Drake wrote:
>>> Well no. PHP is not a professional language because it has no really
>>> design - and that has nothing to do with the fact it beeing a scripting
>>> language. Its a bad scripting language. (Say namespaces for example,
>>> confusing function interfaces, unicode flaws, missing usable frameworks,
>>> silly type handling, quoting hell)
>> - What do you mean by confusing function interfaces and unicode flaws?
> 
> 
> This whole discussion is about a language lawyer and a professional. The
> reality is, professional programmers do use PHP. I would say probably
> more than any other language out there.

This is what I get for writing an email at 4AM after dying at the
top of Sokoban.  Sooo close to the treasure room!

Anyway...

Personal Home Page Tools was designed to personal home pages in the
low-threat environment of 1995, the same year as MySQL.

Whereas some languages and RDBMSs have a firm design philosophy,
both PHP & MySQL were built as personal/small-systems tools, and
then wildly expanded.  Current design, security & feature issues
bear out that there's a lot of ad hocery in both systems.

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

iD8DBQFF3dWbS9HxQb37XmcRAmZOAKDcricsMJl3SUrfJ/s6yzAzIOOCeQCgwcLX
bmxwfTonJ5xZOBSkknR03Po=
=6mAB
-END PGP SIGNATURE-

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

Re: [GENERAL] php professional

[Joshua D. Drake]

>> Answer for this is a bit complex, more newbies howtos, more people
>> saying that is better and so on
> 
> Yeah. Would be good if we can figure out something that would help
> postgresql increase its usage or mind share.


O.k. this is bizarre. One, this discussion belongs on -advocacy not
-general.

Two, you do realize that we have huge mind share right?

> 
> At least so that even the big bosses might be fine with using postgresql.

Oh you mean like a good portion of the largest entities in the world
already do?

Not to be sarcastic but it sounds to me like your boss isn't paying
attention.

I spent Tuesday at one of the largest companies in the world discussion
how we can deploy PostgreSQL enterprise wide.

I spent the week before that teaching a class for a global 2000 that is
about to deploy postgresql (in the next 2 years) to over 5000 installations.

I will spent a week on the east coast doing the same thing for another
division of said company.

We regularly support a company that has over 4000 installations in Europe.

And that is just the tip of the iceberg.

Sincerely,

Joshua D. Drake



-- 

  === 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 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] tsearch2: word position

[Teodor Sigaev]

No, the first X aren't more important, but being able to determine
word proximity is very important for partial phrase matching and
ranking.  The closer the words, the "better" the match, all else being
equal.

exactly

---(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] tsearch2: word position

[Markus Schiltknecht]

Hi,

Mike Rylander wrote:

No, the first X aren't more important, but being able to determine
word proximity is very important for partial phrase matching and
ranking.  The closer the words, the "better" the match, all else being
equal.


Ah, yeah, for word-pairs, that certainly helps.
Thanks.

Regards

Markus

---(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] php professional

[Lincoln Yeoh]

At 12:54 AM 2/23/2007, Rodrigo Gonzalez wrote:
PHP is easy and cheap to start, so there are lots of programmers 
using it, and someone like you, or any other company, can take a 
cheap programmer to do the work. Most of programmer use it with 
mysql, now this is the question to answerwhy?


Maybe there is something else that can be useful for PostgreSQL 
(this list is about postgres right?).


Now, comparing MySQL and PostgreSQL is something that maybe does not 
make sensebut the question is why most newbies go to MySQL and 
not PostgreSQL and say that MySQL is faster, better, easier and so 
on than pgsql


Answer for this is a bit complex, more newbies howtos, more people 
saying that is better and so on


Yeah. Would be good if we can figure out something that would help 
postgresql increase its usage or mind share.


At least so that even the big bosses might be fine with using postgresql.

I'd really rather use postgresql.

Back in the postgres95 days, MySQL just won hands down. Then it 
started getting much better for 6.5.x then from 7.2 onwards things 
started rapidly getting better and better..


Oh well...

Link.


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

Re: [GENERAL] complex referential integrity constraints

[Martijn van Oosterhout]

On Thu, Feb 22, 2007 at 05:28:35PM +0100, Joris Dobbelsteen wrote:
> Even worse, I don't you can guarentee that this constraint is enforced
> at all times. That means, not if you are using triggers.
> The only option seems using foreign keys and put in a lot of redundant
> data.

Err, foreign keys are implemented using triggers, so this statement is
self-contradictary.

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] php professional

[Joshua D. Drake]

>> This whole discussion is about a language lawyer and a professional. The
>> reality is, professional programmers do use PHP. I would say probably
>> more than any other language out there.
> 
> Yes, by the definition that they earn money by doing it.
> 
>> Does that mean that PHP is a technically sound language? No.
>>
>> Does that mean some of these professional programmers are smarter than
>> the language lawyers, because they can get their web apps done, quickly
>> and have a huge community backing them up? Yes.
> 
> Does that mean they would not write better apps in the same time if they
> were so smart using better languages and actual frameworks?

Define better? It is an opinion. Personally I think Ruby on Rails is a
pile of dung that is slow and surrounded by zealots who don't know reality.

I am sure Ruby on Rails people would disagree ;)

> Do we really
> need so many flawed PHP products regulary appear on bugtraq for the
> same reasons every time? (Since the many programmers there just copy and
> paste their code to get their results fast - hey thats smart in some
> sense)

You are arguing something different than I am. I have already conceded
that PHP is not technically a sound language.

But in the real world, it doesn't matter. What matters is, "How can I
get my project done, so that it works"

It doesn't matter what it looks like underneath. It matters what it
looks like on top.

It is a frosted cake made of human feces, but it is still looks
delicious from 3 feet away.


Sincerely,

Joshua D. Drake


-- 

  === 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 2: Don't 'kill -9' the postmaster

Re: [GENERAL] tsearch2: word position

[Mike Rylander]

On 2/22/07, Markus Schiltknecht <[EMAIL PROTECTED]> wrote:

Hello Teodor,

Teodor Sigaev wrote:
> byte offset of word is useless for ranking purpose

Why is a word number more meaningful for ranking? Are the first 100
words more important than the rest? That seems as ambiguous as saying
the first 1000 bytes are more important, no?


No, the first X aren't more important, but being able to determine
word proximity is very important for partial phrase matching and
ranking.  The closer the words, the "better" the match, all else being
equal.

--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(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] php professional

[Joshua D. Drake]

>> P.S. I don't particularly like PHP either, but our company website is
>> coded in it because no other language (for the web) could have done the
>> job at the same TCO.
>>
>>
> 
> PHP is easy and cheap to start, so there are lots of programmers using
> it, and someone like you, or any other company, can take a cheap
> programmer to do the work. Most of programmer use it with mysql, now
> this is the question to answerwhy?

Easy, it was what PHP the project recommended until PHP 5. Also through
the history of the project, MySQL developers (as in the actualy MySQL
people) were also part of PHP.

And no, Command Prompt's website does not use MySQL.

> 
> Maybe there is something else that can be useful for PostgreSQL (this
> list is about postgres right?).
> 
> Now, comparing MySQL and PostgreSQL is something that maybe does not
> make sensebut the question is why most newbies go to MySQL and not
> PostgreSQL and say that MySQL is faster, better, easier and so on than
> pgsql

MySQL is faster, better, easier... out of the box for the applications
that MySQL is good at, which is throw away data and websites.

The key here is that MySQL and PostgreSQL do not compete any more than
PostgreSQL and SQLLite. They are different beasts. They serve different
purposes.

> Why not put some examples with postgresql about that? for example for
> OLTP with 2 GB RAM blah blah

That isn't the purpose of reference documentation. It is the purpose of
a tuning doc or howto and there have been plenty of those on the web.

Sincerely,

Joshua D. Drake


> 
> Just an idea
> 
> Best regards
> 
> Rodrigo
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 


-- 

  === 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 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 (OT: perl)

[Kevin Murphy]

Randal L. Schwartz wrote:

Russ> Take perl for example. I have still yet to see readable Perl code.

You can't read it if you're not familiar with it. 
Seconded.  Perl is like the churkendoose -- hybrid strength, ugly as 
hell, only poultry known that can scare off a fox every time, whole 
barnyard loves having it around.  The better I know it, the better I 
like it.


A language's appeal always depends on your prior language and operating 
system experience, not to mention what you're trying to use it for, how 
you learn it, the support infrastructure around you while learning it, 
how much you're getting paid (in fame, fortune, and/or fun) to program 
in it, how long you've used it, how often you use it, what other 
languages you use alongside of it, who your friends are, how much of a 
sucker you are for what you read in the tech press, how patient you are, 
how creative you are, your tolerance for abstraction, the number and 
quality of programmers you want to be able to potentially contribute to 
or maintain your code, etc, etc.


It's a big barnyard, and it all stinks.  So let's just roll around in 
the mud and have some fun.


-Kevin Murphy


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

Re: [GENERAL] Guarenteeing ordering constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Tom Lane [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 22 februari 2007 17:16
>To: Joris Dobbelsteen
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Guarenteeing ordering constraints 
>
>"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
>> I have some trouble guarenteeing that an ordering constraint is 
>> enforced on the database. On the table ordering (see below) 
>I want to 
>> enforce that for every tuple t, all tuples u where u.position < 
>> t.position this implies u.cumvalue <= t.cumvalue.
>
>I can't think of any reasonable way to enforce that in SQL.  
>Perhaps you should consider restructuring your tables in such 
>a way that this behavior emerges from a constraint that is 
>enforceable --- maybe the cumulative values should be a 
>(materialized?) view on an underlying table that contains 
>individual observations.

Mmm, it seems that I'm beyond the boundaries. I seem to have the habit
of wanting just a little more than is possible. Nevertheless this seemed
simple enough...

Of course there is no way to force the trigger to read all commited rows
and be waiting on the (possibly) required uncommitted ones? This may get
triggers closer to the constraints (like unqiue indexes and foreign
keys).
Seems triggers are more for business intelligence...

Thinking over the idea, I strongly believe restructing is going to cause
more trouble than anything else. Beyond that, there are now enough
constraints that I'm sure I cannot enforce anyways. That is, I cannot
even think about a good way to do it.
The current triggers will prevent bad things most of the time, but not
always. At this point that seems good enough for practical situations.

Example:
You have a printer, that's of some kind of model.
With the model you define what cartridges (colors) you can put in.
You can now replace a cartridge on a printer.
Of course you want to enforce that the color of the cartridge you
replace is valid.

Same problem, and I'm quite sure I cannot enforce it.

I believe something fundamental should change to ensure we can enforce
these constraints. At least, that would be a lot nicer.

- Joris

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

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

Re: [GENERAL] how to generate a list of distinct scalar values from a column which type is array

[David Fetter]

On Thu, Feb 22, 2007 at 12:38:50PM +0100, Sergio Andreozzi wrote:
> Dear all,
> 
> given a column which type is for instance varchar(20)[],

This is almost never a good design.  If you must have an interface
like that, make it VIEW over an aggregate, which you can make
writeable.

> is it possible via SQL to generate the list of distinct scalar
> values?

Yes, but you should fix your design :)

CREATE TABLE foo (ft TEXT[]);

COPY foo(ft) FROM stdin;
{aaa,bb,c}
{,}
{aaa,}
\.

SELECT DISTINCT ft[i]
FROM (
SELECT ft,
generate_series(
array_lower(ft,1),
array_upper(ft,1)
) AS i
FROM foo
) AS bar;

So, yes, you can do it, and no, you shouldn't.

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

Remember to vote!

---(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] php professional

[Tino Wildenhain]

Joshua D. Drake schrieb:

Well no. PHP is not a professional language because it has no really
design - and that has nothing to do with the fact it beeing a scripting
language. Its a bad scripting language. (Say namespaces for example,
confusing function interfaces, unicode flaws, missing usable frameworks,
silly type handling, quoting hell)

- What do you mean by confusing function interfaces and unicode flaws?



This whole discussion is about a language lawyer and a professional. The
reality is, professional programmers do use PHP. I would say probably
more than any other language out there.


Yes, by the definition that they earn money by doing it.


Does that mean that PHP is a technically sound language? No.

Does that mean some of these professional programmers are smarter than
the language lawyers, because they can get their web apps done, quickly
and have a huge community backing them up? Yes.


Does that mean they would not write better apps in the same time if they
were so smart using better languages and actual frameworks? Do we really
need so many flawed PHP products regulary appear on bugtraq for the
same reasons every time? (Since the many programmers there just copy and
paste their code to get their results fast - hey thats smart in some
sense)


Use what works for you, don't listen to others about what language to
code in.


Yes but please dont open all these works for the public ;)

Thanks postgres has not coding and project standards like PHP has,
otherwise we would have a MySQL2 ;)


T.


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

  http://archives.postgresql.org/

Re: [GENERAL] php professional

[Rodrigo Gonzalez]

Joshua D. Drake wrote:

Well no. PHP is not a professional language because it has no really
design - and that has nothing to do with the fact it beeing a scripting
language. Its a bad scripting language. (Say namespaces for example,
confusing function interfaces, unicode flaws, missing usable frameworks,
silly type handling, quoting hell)

- What do you mean by confusing function interfaces and unicode flaws?



This whole discussion is about a language lawyer and a professional. The
reality is, professional programmers do use PHP. I would say probably
more than any other language out there.

Does that mean that PHP is a technically sound language? No.

Does that mean some of these professional programmers are smarter than
the language lawyers, because they can get their web apps done, quickly
and have a huge community backing them up? Yes.

Use what works for you, don't listen to others about what language to
code in.

Sincerely,

Joshua D. Drake

P.S. I don't particularly like PHP either, but our company website is
coded in it because no other language (for the web) could have done the
job at the same TCO.




PHP is easy and cheap to start, so there are lots of programmers using 
it, and someone like you, or any other company, can take a cheap 
programmer to do the work. Most of programmer use it with mysql, now 
this is the question to answerwhy?


Maybe there is something else that can be useful for PostgreSQL (this 
list is about postgres right?).


Now, comparing MySQL and PostgreSQL is something that maybe does not 
make sensebut the question is why most newbies go to MySQL and not 
PostgreSQL and say that MySQL is faster, better, easier and so on than 
pgsql


Answer for this is a bit complex, more newbies howtos, more people 
saying that is better and so on


And I dont agree with that, but there is something I really think is 
good about mysql installation and postgresql should take.
You have 4 or 5 or 6 (I dont remember) examples in doc when you install 
from package, or in contrib I think in source code.so anyone with 
just knowledge about his hardware and usage can tune it.


Why not put some examples with postgresql about that? for example for 
OLTP with 2 GB RAM blah blah


Just an idea

Best regards

Rodrigo

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

Re: [GENERAL] php professional

[Joshua D. Drake]

>> Well no. PHP is not a professional language because it has no really
>> design - and that has nothing to do with the fact it beeing a scripting
>> language. Its a bad scripting language. (Say namespaces for example,
>> confusing function interfaces, unicode flaws, missing usable frameworks,
>> silly type handling, quoting hell)
> 
> - What do you mean by confusing function interfaces and unicode flaws?


This whole discussion is about a language lawyer and a professional. The
reality is, professional programmers do use PHP. I would say probably
more than any other language out there.

Does that mean that PHP is a technically sound language? No.

Does that mean some of these professional programmers are smarter than
the language lawyers, because they can get their web apps done, quickly
and have a huge community backing them up? Yes.

Use what works for you, don't listen to others about what language to
code in.

Sincerely,

Joshua D. Drake

P.S. I don't particularly like PHP either, but our company website is
coded in it because no other language (for the web) could have done the
job at the same TCO.


-- 

  === 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 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Robert Haas [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 22 februari 2007 15:58
>To: Joris Dobbelsteen; elein
>Cc: pgsql-general@postgresql.org
>Subject: RE: [GENERAL] complex referential integrity constraints
>
>The ability to make a foreign key reference a specific partial 
>unique index (rather than just a set of columns that have a 
>unique index) would solve many problems of this type.  As 
>another example, you might have a table where one of the 
>columns is "is_deleted boolean not null".  By creating a 
>partial unique index on the primary key of that table "WHERE 
>NOT is_deleted" and then pointing a foreign key at it, you 
>could enforce that each row in the child table references a 
>parent who isn't deleted.
>
>However, this would break down when there's more one than 
>intermediate step involved.  For example, if you have:
>
>CREATE TABLE animal_type (
>  id   serial,
>   namevarchar(60) not null,
>   is_attacker boolean not null,
>   primary key (id)
>);
>
>CREATE TABLE animal (
>   id  serial,
>   type_id integer not null references animal_type (id),
>   namevarchar(60) not null,
>   primary key (id)
>);
>
>CREATE TABLE mauling (
>   id  serial,
>   attacker_id integer not null references animal (id),
>   victim_id   integer not null references animal (id),
>   attack_time timestamp with time zone not null,
>   primary key (id)
>);
>
>It would be easy to enforce the constraint that the attacker 
>must be an animal of some specific type, but difficult to 
>enforce the constraint that the attacker must be an animal 
>whose type, in turn, has a true value for is_attacker.

Even worse, I don't you can guarentee that this constraint is enforced
at all times. That means, not if you are using triggers.
The only option seems using foreign keys and put in a lot of redundant
data.

>The best idea that I can think of right now to handle multiple 
>levels of tables is to allow FOREIGN KEY constraints to 
>references a VIEW, rather than a table.  Then you could say:
>
>CREATE VIEW attackers AS
>SELECT a.id FROM animal a, animal_type t WHERE a.type_id = 
>t.id AND t.attacker;
>
>...and then FOREIGN KEY (attacker_id) REFERENCES attackers (id).

Perhaps "Alban Hertroys" idea solves this problem a little easier.
However it lacks the possibility to make quick changes later on
(predator is a predator, or you are screwed, no second change).
This is not acceptable in problems where such things are decided after
object creation or might be changed later on.

>This syntax would solve a number of other problems as well, 
>such as requiring that some record in table A has a parent 
>either in table P or in table Q. However, I think this would 
>probably require implementing some kind of materialized view 
>so that you could actually build an index on the view, and 
>that opens up a whole new can of worms, because it's not very 
>difficult to define a view that is costly to update incrementally.

You don't need a materialized view to put a database to its knees. You
can already do that today, with ease. I wouldn't worry too much about
that.
If you mean from a syntax I suggested I do not believe it's the 'right'
way to define an (materialized) view, rather use a trigger-like style of
system. There are some other issues, however.

>The problem is really that there is a pretty large gap between 
>writing a foreign key constraint, which is trivial, and 
>enforcing a constraint using triggers, which is quite a bit 
>more complex (and therefore, easy to screw up), because the 
>foreign key automatically handles all the cases (insert into 
>child table, update of child table, update of parent table, 
>delete from parent table) whereas with triggers you have to 
>address each of those cases individually.

Exactly, that is why I suggested such a system. If its not easy to
enforce constraints, it will never happen properly. Especially if
problems get more complex.

>Unfortunately, 
>something tells me that implementing a more powerful system 
>for foreign key constraints is a non-trivial project, however 
>useful it would be.
>Still, I'd love to see it in the TODO file, too.

Me too, I get the impression that SQL is too weak for most constraints.

- Joris

>...Robert
>
>-Original Message-
>From: Joris Dobbelsteen [mailto:[EMAIL PROTECTED]
>Sent: Thursday, February 22, 2007 8:03 AM
>To: Robert Haas; elein
>Cc: pgsql-general@postgresql.org
>Subject: RE: [GENERAL] complex referential integrity constraints
>
>I partially agree:
>If people CAN do stupid things, they are 'clever' enough to 
>find a way to actually do it. I've seen them destroy things, 
>by just using a system in a way it was not intended. They 
>effectively found a way to blow away the very thing that part 
>was designed

Re: [GENERAL] php professional

[Tim Tassonis]

Tino Wildenhain wrote:


totally off topic,

Tim Tassonis schrieb:

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

My definition is, "toy used/trumpeted by pseudo-professionals as a
professional tool, when it just doesn't measure up".


Boah, here surely speaks a true professional playing in the league of 
Donald Knuth or even Alan Kay, as opposed to all the pseudos like me 
out there.


Is it Assembler or Smalltalk you write your web pages with?


No, python, java ;)

PHP absolutely is a professional tool as a scripting language, of 
course with all the downsides of any scripting language. I'll choose 
php over 


Well no. PHP is not a professional language because it has no really
design - and that has nothing to do with the fact it beeing a scripting
language. Its a bad scripting language. (Say namespaces for example,
confusing function interfaces, unicode flaws, missing usable frameworks,
silly type handling, quoting hell)


- What do you mean by confusing function interfaces and unicode flaws?

- A lot of "professional" languages don't support namespaces and 
frameworks are not part of a language, as I understand a language.


I think we really have different ideas about professional, can you point 
me to a reference of your definition?


I'd definitely say that php is not really an all-purpose language, but 
that doesn't make it unprofessional to me. C is not all-purpose, but 
still professional.




Perl any day, as it is syntactically much cleaner and performs 
sufficiently well for usual scripting needs.


ah... yes. Dont like perl either but its at least carrying some
actual language design.


Like what, as opposed to php?




Of course, I wouldn't write an operating system with it.


Would you write a language with it? :-)


No, I actually solely write scripts with it :-) And web applications.
Guess what most people use php for.



Btw, "professional programmers" can indeed use funny languages
- they are professional by they earning their living with it.


I have yet to see an unfunny language.


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

  http://archives.postgresql.org/

Re: [GENERAL] Guarenteeing ordering constraints

[Tom Lane]

"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
> I have some trouble guarenteeing that an ordering constraint is enforced
> on the database. On the table ordering (see below) I want to enforce
> that for every tuple t, all tuples u where u.position < t.position this
> implies u.cumvalue <= t.cumvalue.

I can't think of any reasonable way to enforce that in SQL.  Perhaps you
should consider restructuring your tables in such a way that this
behavior emerges from a constraint that is enforceable --- maybe the
cumulative values should be a (materialized?) view on an underlying
table that contains individual observations.

regards, tom lane

---(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 10:22 PM 2/22/2007, Tim Tassonis wrote:

Chris wrote:
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.


I do still think it is a bit of an oddity, the concept of the null 
column. From my experience, it creates more problems than it 
actually solves and generally forces you to code more rather than 
less in order to achieve your goals.


But as it is a fundamental, defined part of the sql standard, one 
just has to live with it.


Well it can be useful to have a column like:
foo integer not null default null

That means someone/something must specify a value for foo when doing 
an insert. They can't just hope for the best that there's a default...


I think that works on postgresql but not on MySQL (see back on topic :) ).

Have fun!
Link.


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

Re: [GENERAL] postgresql vs mysql

[Lincoln Yeoh]

At 01:11 PM 2/22/2007, John Smith wrote:

On 2/21/07, Lincoln Yeoh  wrote:

MySQL: the PHP of databases.


'd appreciate if you stick to the subject.
jzs


OK sorry... That was more of a footnote.

Link.





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

Re: [GENERAL] tsearch2: word position

[Markus Schiltknecht]

Hello Teodor,

Teodor Sigaev wrote:

byte offset of word is useless for ranking purpose


Why is a word number more meaningful for ranking? Are the first 100 
words more important than the rest? That seems as ambiguous as saying 
the first 1000 bytes are more important, no?


Or does the ranking work with the word numbers internally to do 
something more clever?


Do you understand why I find the word number inconvenient?

Regards

Markus


---(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] Slony subscription problem

[Martijn van Oosterhout]

On Thu, Feb 22, 2007 at 03:01:59PM -, Chris Coleman wrote:
> Hi,
> 
> Firstly I apologise for the large number of chunks of code and log file
> attached to this post, however without them I don't think there would be
> much point in the post.

There's a mailing list for slony, you might have better luck there.

http://gborg.postgresql.org/mailman/listinfo/slony1-general

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] php professional

[Tino Wildenhain]

totally off topic,

Tim Tassonis schrieb:

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

My definition is, "toy used/trumpeted by pseudo-professionals as a
professional tool, when it just doesn't measure up".


Boah, here surely speaks a true professional playing in the league of 
Donald Knuth or even Alan Kay, as opposed to all the pseudos like me out 
there.


Is it Assembler or Smalltalk you write your web pages with?


No, python, java ;)

PHP absolutely is a professional tool as a scripting language, of course 
with all the downsides of any scripting language. I'll choose php over 


Well no. PHP is not a professional language because it has no really
design - and that has nothing to do with the fact it beeing a scripting
language. Its a bad scripting language. (Say namespaces for example,
confusing function interfaces, unicode flaws, missing usable frameworks,
silly type handling, quoting hell)

Perl any day, as it is syntactically much cleaner and performs 
sufficiently well for usual scripting needs.


ah... yes. Dont like perl either but its at least carrying some
actual language design.


Of course, I wouldn't write an operating system with it.


Would you write a language with it? :-)

Btw, "professional programmers" can indeed use funny languages
- they are professional by they earning their living with it.

T.

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

Re: [GENERAL] postgresql vs mysql

[Randal L. Schwartz]

> "Russ" == Russ Brown <[EMAIL PROTECTED]> writes:

Russ> Take perl for example. I have still yet to see readable Perl code.

I could say the same for greek, and pl/pgsql.

You can't read it if you're not familiar with it.  Please stop bashing Perl
until you've read at least Learning Perl or the equivalent.  Please.  You have
no right.  It's pure prejudice, and usually just parroted from others.

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
 http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

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

   http://archives.postgresql.org/

Re: [GENERAL] Guarenteeing ordering constraints

[Joris Dobbelsteen]

Even this can be violated.
Just create another table and change the first select statement of the
transactions to get data from that table.

Is there any way to actually enforce such ordering constraints under
postgresql?

- Joris

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Joris 
>Dobbelsteen
>Sent: donderdag 22 februari 2007 14:27
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] Guarenteeing ordering constraints
>
>I have some trouble guarenteeing that an ordering constraint 
>is enforced on the database. On the table ordering (see below) 
>I want to enforce that for every tuple t, all tuples u where 
>u.position < t.position this implies u.cumvalue <= t.cumvalue.
>
>Unfortunally postgresql gives me a choice between concurrency 
>or consistency. Given the trigger procedure below (written for 
>simplicity, not speed) it will fail to guarentee consistency 
>when using serializable isolation.
>Just load the initial dataset (the failing operations are only 
>to test the constraints).
>The next step is to execute transactions 1 and 2 in parallel 
>(step by step). This will cause the constraint to be violated.
>
>It does work in the default isolation level (read committed).
>Alternatively one can use the second LOCK statement in the 
>trigger, which does an ACCESS EXCLUSIVE. Obviously this 
>completely defeats concurrency (causes one of the transactions 
>to be retried).
>
>Is there any way that will both guarentee consistency and 
>provide some better concurrency?
>
>- Joris
>
>===
>
>CREATE PROCEDURAL LANGUAGE plpgsql;
>
>CREATE TABLE ordering (
>"position" integer NOT NULL,
>cumvalue integer NOT NULL
>);
>
>CREATE FUNCTION tr_ordering_cumvalue_simple() RETURNS "trigger"
>AS $$BEGIN
>   -- position is unique (index enforced)
>   -- cumvalue constraint
>   --
>   -- Let p,q be an element of ordering,
>   -- where p.position < q.position implies p.cumvalue <= 
>q.cumvalue
>
>   -- Thus for every new tuple t
>   -- we need to ensure
>   -- For all p (of ordering) p.position < t.position 
>implies p.cumvalue <= t.cumvalue
>   --  andp.position > t.position implies
>p.cumvalue >= t.cumvalue
>   --
>   -- note (p implies q) <=> (!p or q)
>
>   -- lock full table, no others updating it...
>   LOCK TABLE ordering IN EXCLUSIVE MODE;
>   --LOCK TABLE ordering IN ACCESS EXCLUSIVE MODE;
>
>   IF EXISTS (SELECT *
>  FROM ordering o
>  WHERE-- violates constraints
>   (o.position < NEW.position and o.cumvalue >
>NEW.cumvalue)
>  OR
>   (o.position > NEW.position and o.cumvalue <
>NEW.cumvalue)
> )
>   THEN
>   RAISE EXCEPTION 'Constraint violation detected 
>by %', TG_name;
>   END IF;
>
>   RETURN NEW;
>END$$
>LANGUAGE plpgsql;
>
>CREATE TRIGGER tr_ordering_cumvalue
>BEFORE INSERT OR UPDATE ON ordering
>FOR EACH ROW
>EXECUTE PROCEDURE tr_ordering_cumvalue_simple();
>
>
>-- initial dataset
>BEGIN;
>DELETE FROM ordering;
>INSERT INTO ordering VALUES (0,0);
>INSERT INTO ordering VALUES (10,100);
>INSERT INTO ordering VALUES (20,200);
>COMMIT;
>
>-- failing operation
>BEGIN;
>INSERT INTO ordering VALUES (-1,1);
>INSERT INTO ordering VALUES (15,1);
>INSERT INTO ordering VALUES (16,201);
>INSERT INTO ordering VALUES (21,-1);
>ROLLBACK;
>
>-- transaction 1
>BEGIN ISOLATION LEVEL SERIALIZABLE;;
>SELECT * FROM ordering;
>INSERT INTO ordering VALUES (19,101);
>SELECT * FROM ordering;
>COMMIT;
>
>-- transaction 2
>BEGIN ISOLATION LEVEL SERIALIZABLE;
>SELECT * FROM ordering;
>INSERT INTO ordering VALUES (11,199);
>SELECT * FROM ordering;
>COMMIT;
>
>---(end of 
>broadcast)---
>TIP 6: explain analyze is your friend
>

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

   http://archives.postgresql.org/

Re: [GENERAL] Infinite loop in transformExpr()

[Tom Lane]

Fernando Schapachnik <[EMAIL PROTECTED]> writes:
> En un mensaje anterior, Tom Lane escribió:
>> Fernando Schapachnik <[EMAIL PROTECTED]> writes:
>>> I've stumbled upon what seems to be a core-dumping infinite recursion 
>>> in transformExpr(), on 8.1.6.
>> 
>> A test case would help.

> The culprit query looks like:
>   WHERE join condition AND
>   int_key IN (enumeration of aprox. 16000 values here)

PG versions before 8.2 don't handle very long IN lists particularly
well.  This query will take a fair amount of stack space to parse, not
to mention an unreasonably long time to plan.  (You should consider
putting the 16000 values in a temp table and doing a join, instead.)

> Running the query in this scenario (reasonably) gives:
> ERROR:  stack depth limit exceeded
> HINT:  Increase the configuration parameter "max_stack_depth".
> So I'm unsure why it explodes in production.

Most likely, the production machine has a kernel-enforced stack limit
setting that is less than what "max_stack_depth" claims.  Up till recently
(8.2 I think), we didn't make any effort to verify that "max_stack_depth"
was set to a sane value.  If it's too high you will get crashes rather
than "stack depth limit exceeded", because overrunning the kernel limit
is typically treated as a SIGSEGV.

> (gdb) bt
> #0  0x284eb37b in kill () from /lib/libc.so.5
> #1  0x284e0422 in raise () from /lib/libc.so.5
> #2  0x28552c1b in abort () from /lib/libc.so.5
> #3  0x290b6a7c in pthread_testcancel () from /usr/lib/libpthread.so.1
> #4  0x290b3067 in pthread_setconcurrency () from 
> /usr/lib/libpthread.so.1
> #5  0x290b2e87 in pthread_setconcurrency () from 
> /usr/lib/libpthread.so.1
> #6  0x290b627a in pthread_testcancel () from /usr/lib/libpthread.so.1
> #7  0x290b740a in __error () from /usr/lib/libpthread.so.1
> #8  0x2909e7ae in ?? () from /usr/lib/libpthread.so.1
> #9  0x282a5845 in find_symdef () from /libexec/ld-elf.so.1
> #10 0x282a61aa in dlopen () from /libexec/ld-elf.so.1
> #11 0x08164d38 in BSD44_derived_dlopen ()
> #12 0x081f9550 in load_external_function ()
> #13 0x081fa06c in fmgr_info_cxt ()

Hm.  It would appear that you are loading some custom code that sucks
pthread support into the backend.  This is generally a bad idea in any
case, as the backend code is not designed for threaded operation.  But
the reason it seems relevant is that thread support often causes a
decrease in the effective stack limit (because it's slicing up the stack
area for use by multiple threads).  I'd suggest trying to fix the link
dependencies of your code to avoid sucking in libpthread.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] postgresql vs mysql

[Tim Tassonis]

Rich Shepard wrote:

On Thu, 22 Feb 2007, Tim Tassonis wrote:

I do still think it is a bit of an oddity, the concept of the null 
column.

From my experience, it creates more problems than it actually solves and
generally forces you to code more rather than less in order to achieve
your goals.


Tim,

  Long ago, a lot of database applications used 99, or 999, or -1 to
indicate an unknown value. However, those don't fit well with a textual
field and they will certainly skew results if used in arithmetic
calculations in numeric fields.


I remember, my first database to write stuff for was an IMB IMS 
hierarchical/network one.




  The concept of NULL representing an unknown value, and therefore one that
cannot be compared with any other value including other NULLs, is no
different from the concept of zero which was not in mathematics for the
longest time until some insightful Arab mathematician saw the need for a
representation of 'nothing' in arithmetic and higher mathematics.

  There was probably resistance to that idea, too, as folks tried to wrap
their minds around the idea that 'nothing' could be validly represented 
by a

symbol and it was actually necessary to advance beyond what the Greeks and
Romans -- and everyone else -- could do. Now, one would be thought a bit
strange to question the validity of zero.


That's one point for me, then!. NULL exactly is _not_ the equivalent the 
the number 0, but the mentioned strange symbol that has to be treated 
specially and does not allow normal calculation, like '0' does in 
mathematics. I don't know how many times I had to write a query that 
ends with:


- or column is null
- and column is not null

exactly because it is a special symbol. In mathematics, the only special 
case for zero that springs to my mind is the division of something by 
zero (I'm by no means a mathematician).


As a completely irrelevant sidenote to the discussion, I'm greek and not 
arabic, but I certinly do accept the superiority of the arabic notation.




  NULL solves as many intransigent problems with digital data storage and
manipulation in databases as zero did in the realm of counting.


As I said, I don't deny it solves some problems (that could be solved in 
a different way, too), but in my opinion, it creates more (that also can 
 be solved, as above examples show).


Tim


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

  http://archives.postgresql.org/

Re: [GENERAL] Where art thou pg_clog?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> The real bottom line here, and one I'll reiterate every chance I get,
> is that we don't make updates to back branches because we're too bored
> to have anything else to do.  If you're on 8.1.5, and the current
> release in that branch is 8.1.8, then you're missing some bug fixes
> that are probably significant.

Just as a data point, I came across this very same problem (corrupted 
tuple header, invalid xlog file) on an 8.1.3 system that was NOT 
running autovacuum (and never had). Amazingly, this occured on the 
very same day as the original poster. The server was upgraded to 8.2.3, 
after some creating-bogus-xlog-file pain to extract all the data, and 
all is well again.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200702221021
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iD8DBQFF3bW8vJuQZxSWSsgRA45cAKCQYcPdmqvNh9KRBGNsm/YjycmqFQCgzIil
nUXZs7wIJvkxs6RaBTW5cKA=
=V0YI
-END PGP SIGNATURE-



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

[GENERAL] Slony subscription problem

[Chris Coleman]

Hi,

Firstly I apologise for the large number of chunks of code and log file
attached to this post, however without them I don't think there would be
much point in the post.

I have a slony replication system set up and working (slony 1.1.5,
Postgres 8.1), and am trying to add a new node to it as I have done
previously.  The system is simple, it has 1 source node, and then 5
receiver nodes.  In order to add the new node I am doing the following:

1) I have created the DB on the new receiver, using the same table
definitions as on the host.  

2) I then run a slonik script below to tell the two nodes how to
communicate:

slonik <<_EOF_
#--
# define the namespace the replication system
#--
cluster name = $CLUSTERNAME;

#--
# admin conninfo's are used by slonik to connect to the nodes
one for each
# node on each side of the cluster, the syntax is that of
PQconnectdb in
# the C-API
# --
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST
user=$REPLICATIONUSER password=$REPLICATIONPASS';
node $SLAVEDBID admin conninfo = 'dbname=$SLAVEDBNAME
host=$SLAVEHOST user=$REPLICATIONUSER password=$REPLICATIONPASS';

#--
# Create the slave node and tell the 2 nodes how to connect to
# each other and how they should listen for events.
#--
store node (id=$SLAVEDBID, comment = 'Slave node $SLAVEDBNAME');
store path (server = 1, client = $SLAVEDBID,
conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER');
store path (server = $SLAVEDBID, client = 1,
conninfo='dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER');
store listen (origin= 1, provider = 1, receiver = $SLAVEDBID);
store listen (origin= $SLAVEDBID, provider = $SLAVEDBID,
receiver = 1);
_EOF_

3) I then start the slon daemon for the new node (id = 30), all looks ok
in the logs here (see below).

4) I then execute the following slonik script to begin the replication,
eg subscribe the new node to the source node.  Again everything  looks
fine in the logs, with confirmation of copy_set taking 0.078secs etc.

slonik <<_EOF_
# 
# This defines which namespace the replication system uses
# 
cluster name = $CLUSTERNAME;

# 
# Admin conninfo's are used by the slonik program to connect
# to the node databases. So these are the PQconnectdb arguments
# that connect from the administrators workstation (where
# slonik is executed).
# 
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST
user=$REPLICATIONUSER password=$REPLICATIONPASS';
node $SLAVEDBID admin conninfo = 'dbname=$SLAVEDBNAME
host=$SLAVEHOST user=$REPLICATIONUSER password=$REPLICATIONPASS';

# 
# Node 2 subscribes set 1
# 
subscribe set ( id = 1, provider = 1, receiver = $SLAVEDBID,
forward = no);
_EOF_

However when I come to look at the receiver DB there is no data in any
of the three replicated tables. (The amount of data is small <100MB)

From digging around I have discovered that: 

sl_table is empty on the receiver, but not on all other nodes
sl_subscribe has a correct looking line in it connecting the source node
and the new receiver node.

Any help in resolving this would be much appreciated as I've been
banging my head against the wall for a good few hours now.  I may be
slow to reply as I'm away tomorrow (Fri) but will respond to any answers
ASAP on Monday morning.

Many thanks,

Chris Coleman


//
// HOST logfile
//

@400045dd8f172d3b9cdc 2007-02-22 12:39:41 GMT CONFIG main: slon
version 1.1.5 starting up
@400045dd8f172dd037d4 2007-02-22 12:39:41 GMT CONFIG main: local
node id = 1
@400045dd8f172dd81f44 2007-02-22 12:39:41 GMT CONFIG main: launching
sched_start_mainloop
@400045dd8f172ddaf1ec 2007-02-22 12:39:41 GMT CONFIG main: loading
current cluster configuration
@400045dd8f172e133c14 2007-02-22 12:39:41 GMT CONFIG storeNode:
no_id=2 no_comment='Slave node eurobrats.pirates_current.bugs'
@400045dd8f172e13e7f4 2007-02-22 12:39:41 GMT CONFIG storeNode:
no_id=5 no_comment='Slave node eurobrats.middleware.bugs'
@400045dd8f172e14499c 2007-02-22 12:39:41 GMT CONFIG storeNode:
no_id=6 no_comment='Slave node eurobrats.general.practice'
@400045dd8f172e14a75c 2007-02-22 12:39:41 GMT CONFIG storeNode:
no_id=7 no_comment='Slave node euroalert'
@400045dd8f172e15051c 2007-02-22 12:39:41 GMT CONFIG storeNode:
no_id=8 no_comment='Slave node eurobrats.pirates_nextgen.bugs'
@400045dd8f172e203c34 2007-02-22 12:39:41 GMT CONFIG storePath:
pa_server=8 pa_client=1
pa_conninfo="dbname=eurobrats.pirates_nextgen.bugs
host=bratboxuser=postgres" pa_connretry=10
@400045dd8f172e21883c 2007-02-22 12:39:41 GMT CONFIG storePath:
pa_serv