Re: [GENERAL] SQL Transaction related

[A. Kretschmer]

am  Thu, dem 10.05.2007, um  2:24:40 -0400 mailte Harpreet Dhaliwal folgendes:
> Hi,
> 
> I have a transaction like following:
> 
> BEGIN
> 
>  INSERT INTO tbl_xyz VALUES (val1, val2);
>
> SELECT INTO wid MAX(val1) FROM tbl_xyz;
> 
> END;
> 
> My question is in the SELECT INTO statement, will I get the value of val1 from
> the INSERT INTO in the same transaction

No, you get MAX(val1). If the last inserted record contains this value,
than you get it.


> even though the transaction has not ended yet.
> I think no.

Within a transaction you see everything that has gone in this
transaction. Why not?


> How would I get that latest value of val1 in the same transaction because its
> not committed yet as the transaction has not ended.

We have MVCC, and you see your own snapshot, including all results
within this transaction.


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 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] SQL Transaction related

[Uwe C. Schroeder]

Yes it will. Everything INSIDE ONE transaction is visible to that exact 
transaction. So in your scenario the val1 from the select will see what was 
inserted - just any other transaction won't unless the current one is 
committed.

Uwe


On Wednesday 09 May 2007, Harpreet Dhaliwal wrote:
> Hi,
>
> I have a transaction like following:
>
> BEGIN
>
>  INSERT INTO tbl_xyz VALUES (val1, val2);
>
> SELECT INTO wid MAX(val1) FROM tbl_xyz;
>
> END;
>
> My question is in the SELECT INTO statement, will I get the value of val1
> from the INSERT INTO in the same transaction
> even though the transaction has not ended yet.
> I think no.
> How would I get that latest value of val1 in the same transaction because
> its not committed yet as the transaction has not ended.
>
> Thanks,
>
> ~Harpreeet



--
Open Source Solutions 4U, LLC   1618 Kelly St
Phone:  +1 707 568 3056 Santa Rosa, CA 95401
Cell:   +1 650 302 2405 United States
Fax:+1 707 568 6416

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

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

[GENERAL] SQL Transaction related

[Harpreet Dhaliwal]

Hi,

I have a transaction like following:

BEGIN

INSERT INTO tbl_xyz VALUES (val1, val2);

   SELECT INTO wid MAX(val1) FROM tbl_xyz;

END;

My question is in the SELECT INTO statement, will I get the value of val1
from the INSERT INTO in the same transaction
even though the transaction has not ended yet.
I think no.
How would I get that latest value of val1 in the same transaction because
its not committed yet as the transaction has not ended.

Thanks,

~Harpreeet

Re: [GENERAL] In theory question

[Naz Gassiep]

> I have always found MySQL's query cache to be utterly useless.
>
> Think about it this way :
>
> It only works for tables that seldom change.
> It does not work for big tables (like the posts table of a forum)
> because the cache would have to be huge.
>
> So, the most frequent usage of MySQL's query cache is for dumb
> applications who use, for instance, PHP, store their configuration in
> MySQL, and reload it on each and every page with a SELECT * FROM
> configuration.
>
> In this case, you save the query time, but you don't save : the
> roundtrip between PHP and the database, extracting query results,
> building objects from them, time spent in ORMs, etc.
>
> A much better solution is to do your own caching, for instance
> using shared memory in the application server, and then you cache
> native language objects. You not only save the query time, but also
> all the time spent building those objects on every page load.
This was actually my original question. In my web page, I cache the
config, user preferences and other static material in session vars and
only rerun the function that fetches them if the app ever changes them

If you are clever about db fetches in this way and store as much stuff
in session vars (which is just like storing it in a memcached instance
really) is there much or even any benefit in running memcached?

---(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] IP Address Validation

[Michael Fuhr]

On Thu, May 10, 2007 at 12:22:37AM -0400, Jasbinder Singh Bali wrote:
> I need to check whether the input string is in ip address format or not in
> one of my pl/pgsql functions.
> What function should be used to accomplish this
> 
> I tried using
> if inet(strInput)
> 
> However, it throws an exception if the input string is not in IP address
> format.
> 
> I could have caught this exception but really don't know what type of
> exception category would this exception come under..

psql test
test=> \set VERBOSITY verbose
test=> select inet('junk');
ERROR:  22P02: invalid input syntax for type inet: "junk"

http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html

22P02   INVALID TEXT REPRESENTATION invalid_text_representation

-- 
Michael Fuhr

---(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] Replication for PG 8 recommendations

[David Wall]

On Wed, 2007-05-09 at 14:40 -0700, David Wall wrote:
  
Is there a "preferred" replication system for PG 8 db users?  Obviously, 
we're looking for robustness, ease of operations/installation, low 
latency and efficient with system and network resources, with an active 
open source community being preferred.


Jeff Davis wrote:
http://www.postgresql.org/docs/8.2/static/high-availability.html
  
Thanks.  I've seen the options and was hoping for grunt-level 
realities.  Many projects seem to have fallen by the wayside over time. 

My first impression was towards a Slony-I type solution, but I need 
large objects and would prefer schema updates to be automatic.  I was 
hoping to hear back on any pitfalls or preferences or "how I'd do it if 
I could do it again" type stories.  We mostly need it for disaster 
recovery since we're looking to improve upon our current nightly 
backup/syncs in which we pg_dump the database, SCP it to the backup, 
then pg_restore on the backup.  It's possible WAL copying will do it, 
too, but don't know if people find this workable or not.


David

[GENERAL] IP Address Validation

[Jasbinder Singh Bali]

Hi,
I need to check whether the input string is in ip address format or not in
one of my pl/pgsql functions.
What function should be used to accomplish this

I tried using
if inet(strInput)

However, it throws an exception if the input string is not in IP address
format.

I could have caught this exception but really don't know what type of
exception category would this exception come under..

Any kind of help would be highly appreciated.

Thanks,

Jas

Re: [GENERAL] Views- Advantages and Disadvantages

[Brent Wood]

Joshua D. Drake wrote:

Brent Wood wrote:

Ashish Karalkar wrote:

Thanks Brent for your replay,


What about the Disadvantages, Performance issues?


As far as I'm aware, performance is the only real disadvantage.


What performance are we talking about here? Executing from a view 
although has *some* overhead, I don't even know that it is worth 
considering in most cases.


Sorry, I didn't express that clearly...

A view as a filter on a single table, or a simple two table join is 
generally quite fast enough, and has no noticeable overhead over a query.


A complex self relation plus some joins instantiated via a view can be 
slow. But a query to produce the same output from the same underlying 
tables will be just as slow.


In such cases, (like some data warehousing) a redesigned, denormalised 
table structure can be implemented to provide the same "look" as the 
view would have, but as a physical table, well indexed and much faster 
than the view, without the joins being required.


My comment was meant to indicate that a complex view wihich is too slow 
may be replaced by a denormalised physical table, which is faster, not 
that a view is significantly slower than the same query upon the same 
tables.


To paraphrase, the view/query performance is dependent on the underlying 
table structures & relationships, and these are what may need to change 
if a view is too slow. In many cases, such changes can make the original 
view redundant.




Cheers,

 Brent

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/09/07 20:21, Dann Corbit wrote:
>> -Original Message-
>> From: [EMAIL PROTECTED] [mailto:pgsql-general-
>> [EMAIL PROTECTED] On Behalf Of Ron Johnson
>> Sent: Wednesday, May 09, 2007 6:07 PM
>> To: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Views- Advantages and Disadvantages
>>
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 05/09/07 15:18, Dann Corbit wrote:
>> [snip]
>>> That is a significant achievement, since many database systems do
> not
>>> have that ability.
>> Maybe (probably!) back in the Oracle 6 days, but cost-based
>> optimizers have done this for *years*.
> 
> I work mostly with legacy database systems, so I am surely behind the
> times here.

As do I, but it (Rdb/VMS) has had a CBO for 20+ years.

(Oracle bought Rdb/VMS from DEC back in 1994, and I know for certain
that a lot of the Rdb engineers were transferred straight into the
RDBMS engineering group.)

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

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

iD8DBQFGQnYVS9HxQb37XmcRAjODAKCoiEXClQ9PyWT9sEGfn21qbRvRfwCgnXYM
oYUrnVBwuSadIJfDDEDln2I=
=i2pZ
-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: [GENERAL] Views- Advantages and Disadvantages

[Dann Corbit]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Ron Johnson
> Sent: Wednesday, May 09, 2007 6:07 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Views- Advantages and Disadvantages
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 05/09/07 15:18, Dann Corbit wrote:
> [snip]
> >
> > That is a significant achievement, since many database systems do
not
> > have that ability.
> 
> Maybe (probably!) back in the Oracle 6 days, but cost-based
> optimizers have done this for *years*.

I work mostly with legacy database systems, so I am surely behind the
times here.

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Michael Glaesemann]

On May 9, 2007, at 19:58 , Joshua D. Drake wrote:


Brent Wood wrote:

Ashish Karalkar wrote:

Thanks Brent for your replay,


What about the Disadvantages, Performance issues?

As far as I'm aware, performance is the only real disadvantage.


What performance are we talking about here? Executing from a view  
although has *some* overhead, I don't even know that it is worth  
considering in most cases.


Two people now have stated without much qualification that views have  
some kind of associated performance (Brent Woods) or optimization  
(Dann Corbit) penalty. Where does this idea come from? Views in  
PostgreSQL are just rewritten with the view query inlined! There's  
not much overhead there AIUI.


Michael Glaesemann
grzm seespotcode net



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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/09/07 15:18, Dann Corbit wrote:
[snip]
> 
> That is a significant achievement, since many database systems do not
> have that ability.

Maybe (probably!) back in the Oracle 6 days, but cost-based
optimizers have done this for *years*.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

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

iD8DBQFGQnAfS9HxQb37XmcRAlAUAKCb0GVM3ciXvoY6ACu8z7rl6ntTPwCgzP9S
8taWNM58VyBMaChvOfuVKaE=
=7OGo
-END PGP SIGNATURE-

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Joshua D. Drake]

Brent Wood wrote:

Ashish Karalkar wrote:

Thanks Brent for your replay,


What about the Disadvantages, Performance issues?


As far as I'm aware, performance is the only real disadvantage.


What performance are we talking about here? Executing from a view 
although has *some* overhead, I don't even know that it is worth 
considering in most cases.


Joshua D. Drake



I tend to break DB design into stages:

ER modelling to define the entities/relationships the DB needs to 
store/represent

Normalize this to maximise data integrity & minimise duplication/redundancy
De-normalise to meet performance & access requirements.


In the latter stage, views are generally used, unless there are 
performance restrictions,

when a de-normalised schema may be applied.

Cheers,

 Brent

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




--

  === 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] Views- Advantages and Disadvantages

[Brent Wood]

Ashish Karalkar wrote:

Thanks Brent for your replay,


What about the Disadvantages, Performance issues?


As far as I'm aware, performance is the only real disadvantage.

I tend to break DB design into stages:

ER modelling to define the entities/relationships the DB needs to 
store/represent

Normalize this to maximise data integrity & minimise duplication/redundancy
De-normalise to meet performance & access requirements.


In the latter stage, views are generally used, unless there are 
performance restrictions,

when a de-normalised schema may be applied.

Cheers,

 Brent

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Richard Broersma Jr]

--- Reece Hart <[EMAIL PROTECTED]> wrote:
> I believe that you're mistaken, and you can see it rather easily by
> explaining a select on a view (or even a view of views). For example:
> View definition:
>  SELECT pa.palias_id, pv.pseq_id, pa.origin_id, pa.alias,
> pa.descr, pa.tax_id, pv.ref_pseq_id, pv.added
>FROM paliasorigin pa, pseqalias pv
>   WHERE pv.palias_id = pa.palias_id AND pv.is_current = true;

if you're only selectivity is "is_current = true", you will get a seq. scan no 
matter what. 
However, I bet your explain plan would be much better if you added something 
like:

SELECT * 
  FROM View 
 WHERE origin_id = ;

as long as you had an index on origin_id.

Regards,
Richard Broersma Jr.

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

   http://archives.postgresql.org/

Re: [GENERAL] Replication for PG 8 recommendations

[Jeff Davis]

On Wed, 2007-05-09 at 14:40 -0700, David Wall wrote:
> Is there a "preferred" replication system for PG 8 db users?  Obviously, 
> we're looking for robustness, ease of operations/installation, low 
> latency and efficient with system and network resources, with an active 
> open source community being preferred.

http://www.postgresql.org/docs/8.2/static/high-availability.html

Regards,
Jeff Davis


---(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] In theory question

[David Fetter]

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:
> Naz Gassiep wrote:
> >Hannes Dorbath wrote:
> >>I think this is close to what MySQL's query cache does. The question
> >>is if this should be the job of the DBMS and not another layer. At
> >>least the pgmemcache author and I think that it's better done outside
> >>the DBMS. See
> >>http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for the idea.
> >This is exactly what I was asking about. So my theoretical idea has
> >already been implemented. Now if only *all* my ideas were done for me by
> >the time I came up with them :)
> 
> Then you wouldn't be able to eventually patent them ;)

You have no faith in the patent system ;)

Cheers,
D (remember, Mr. Naz is from a country where somebody patented the wheel.)
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

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

Re: [GENERAL] CentOS 5, pg8.4.2, could not read time zone file

[Devrim GÜNDÜZ]

Hi,

On Wed, 2007-05-09 at 12:51 -0400, Wade Hampton wrote:
> The build of rhel src rpm failed due to "This platform is not thread
> safe.  Check the file 'config.lg' fo rthe exact reason." 

Is this CentOS 5 final? I saw this issue in a beta release of RHEL5, but
I saw that it is fixed in final release.

I could install and use RHEL5 PGDG RPMs on CentOS 5, btw.
  
Regards,
-- 
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/




signature.asc
Description: This is a digitally signed message part

[GENERAL] Replication for PG 8 recommendations

[David Wall]

Is there a "preferred" replication system for PG 8 db users?  Obviously, 
we're looking for robustness, ease of operations/installation, low 
latency and efficient with system and network resources, with an active 
open source community being preferred.


Thanks,
David

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

  http://archives.postgresql.org/

Re: [GENERAL] In theory question

[Joshua D. Drake]

Alvaro Herrera wrote:

Joshua D. Drake wrote:

Karsten Hilbert wrote:

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:


This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)

I think you are overly optimistic  ;-)
You obviously haven't reviewed the US patent system. I just patented "A 
textual method for conveying expression without using any standardized 
language and only predetermined, but arbitrary symbols."


That's already patented by Microsoft.


No they patented:


A textual method for conveying expression without using standardized
language and only predetermined, but arbitrary symbols."



--

  === 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] In theory question

[Alvaro Herrera]

Joshua D. Drake wrote:
> Karsten Hilbert wrote:
> >On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:
> >
> >>>This is exactly what I was asking about. So my theoretical idea has
> >>>already been implemented. Now if only *all* my ideas were done for me by
> >>>the time I came up with them :)
> >>Then you wouldn't be able to eventually patent them ;)
> >
> >I think you are overly optimistic  ;-)
> 
> You obviously haven't reviewed the US patent system. I just patented "A 
> textual method for conveying expression without using any standardized 
> language and only predetermined, but arbitrary symbols."

That's already patented by Microsoft.

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

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Reece Hart]

On Wed, 2007-05-09 at 12:02 -0700, Dann Corbit wrote:
> Views can hide important information from the optimizer (especially
> index information). 

I believe that you're mistaken, and you can see it rather easily by
explaining a select on a view (or even a view of views). For example:

[EMAIL PROTECTED]> \d palias
View "unison.palias"
   Column|   Type   | Modifiers 
-+--+---
 palias_id   | integer  | 
 pseq_id | integer  | 
 origin_id   | integer  | 
 alias   | text | 
 descr   | text | 
 tax_id  | integer  | 
 ref_pseq_id | integer  | 
 added   | timestamp with time zone | 
View definition:
 SELECT pa.palias_id, pv.pseq_id, pa.origin_id, pa.alias,
pa.descr, pa.tax_id, pv.ref_pseq_id, pv.added
   FROM paliasorigin pa, pseqalias pv
  WHERE pv.palias_id = pa.palias_id AND pv.is_current = true;


[EMAIL PROTECTED]> explain select * from palias where tax_id=9606;
 QUERY
PLAN 


 Hash Join  (cost=701397.95..1634572.27 rows=1293890 width=137)
   Hash Cond: (pv.palias_id = pa.palias_id)
   ->  Seq Scan on pseqalias pv  (cost=0.00..474670.85
rows=20706650 width=20)
 Filter: is_current
   ->  Hash  (cost=647199.80..647199.80 rows=1692012 width=121)
 ->  Bitmap Heap Scan on paliasorigin pa
(cost=33808.65..647199.80 rows=1692012 width=121)
   Recheck Cond: (tax_id = 9606)
   ->  Bitmap Index Scan on paliasorigin_tax_id_idx
(cost=0.00..33385.65 rows=1692012 width=0)
 Index Cond: (tax_id = 9606)
(9 rows)


Long ago I compared a few views with their inlined counterparts and the
upshot is that there is exactly or practically zero difference.

-Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0


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

[GENERAL] WAL file internals and why a 64 bit will not work on a 32 bit

[Dhaval Shah]

I do know that WAL files taken from a 64 bit OS will not work on a 32
bit OS. However I have to prepare a technical answer to this.

That is, questions like - why a WAL file from 64 bit will not work in
32 bit. Also does the WAL file differ for same architecture but
different kind of partitions?

Regards
Dhaval

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

Re: [GENERAL] In theory question

[Joshua D. Drake]

Karsten Hilbert wrote:

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:


This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)


I think you are overly optimistic  ;-)


You obviously haven't reviewed the US patent system. I just patented "A 
textual method for conveying expression without using any standardized 
language and only predetermined, but arbitrary symbols."


I noticed you used the ;-). You owe me 75.00.

Joshua D. Drake



Karsten



--

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

Re: [GENERAL] In theory question

[Karsten Hilbert]

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:

> >This is exactly what I was asking about. So my theoretical idea has
> >already been implemented. Now if only *all* my ideas were done for me by
> >the time I came up with them :)
> 
> Then you wouldn't be able to eventually patent them ;)

I think you are overly optimistic  ;-)

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(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] Problem with data corruption and psql memory usage

[Scott Marlowe]

On Wed, 2007-05-09 at 11:18, Gerhard Wiesinger wrote:
> Hello Tom!
> 
> I don't think this is a hardware problem. Machine runs 24/7 for around 4 
> years without any problems, daily backup with GBs of data to it, 
> uptimes to the next kernel security patch, etc.
> 
> The only problem I could believe is:
> I'm running the FC7 test packages of postgresql in FC6 and maybe there is 
> a slight glibc library conflict or any other incompatibility.

While I agree with Tom that you should look at recompiling the fc7
packages to fc6, hardware does break in strange ways sometimes.  A piece
of dust in just the right place, a bit of heat sink compound that
finally migrated onto a circuit trace.

I'd test the hardware to be sure.

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

   http://archives.postgresql.org/

Re: [GENERAL] Dangers of fsync = off

[Brad Nicholson]

On Wed, 2007-05-09 at 08:26 -0600, Scott Ribe wrote:
> > I still wouldn't trust Slony with fsync off.  Another scenario would be
> > the Slony trigger writes a change to the Slony DB, the db crashes before
> > it gets committed to disk.  When the DB is started, no errors prevent
> > startup, but that transaction is lost.
> 
> I'm not sure, but I think the questioner was proposing a policy of "if it
> crashes, we go to the standby, no attempt at recovery, ever", and I think
> that would be safe.

Just make sure that there is no way that the database would come back up
after the crash.  If it did, the slons could pick up and cause you
trouble.  

If you disable all start up scripts, and operate under the assumption
that crash=corruption=failover to Slony replica, you should be okay.
You will lose whatever transactions were not replicated to the
subscriber, but that's inherent to async replication.

> And, personally, given my experience with pg, I think that's reasonable.
> Because the day I see pg crash I'm going to assume I have a hardware problem
> ;-)

If you care about your data, leave fsync on.  Period.  If you can accept
the potential for data loss, and you've proven that there is a
worthwhile performance benefit from turning it off (which there may not
be), and you gotten your boss/clients/stakeholders to sign off
(preferably in writing) that data loss is acceptable if the db crashes,
then go ahead and turn it off.

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.


---(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] Views- Advantages and Disadvantages

[Joshua D. Drake]

Views can hide important information from the optimizer (especially
index information).

Really? AIUI, views-at least in PostgreSQL-are implemented using
PostgreSQL's rule system: the entire query is rewritten to include
the view query, and the optimizer sees the rewritten query. What the
optimizer sees is the same as if the view were inlined in the
original query.


That is a significant achievement, since many database systems do not
have that ability.


Another advantage of views is the ability to give zero rights to access 
the objects the view is on, but allow the user to execute the view 
itself. This allows limiting the view to not only a specific where 
clause but also specific columns the user will be able to view.


Joshua D. Drake



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




--

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

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


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

Re: [GENERAL] Views- Advantages and Disadvantages

[Joshua D. Drake]

Dann Corbit wrote:

-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Ashish Karalkar
Sent: Wednesday, May 09, 2007 1:36 AM
To: Andrej Ricnik-Bay; Ron Johnson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Views- Advantages and Disadvantages

Thanks All for your replies,

But then dont we have any disadvantage of using View???


Views can hide important information from the optimizer (especially
index information).


I don't believe that is true in PostgreSQL's case.

Joshua D. Drake




---(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 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] User restrictions

[John DeSoi]

On May 8, 2007, at 1:10 PM, ebmb wrote:


 how can I make user restrictions to commands like "\du; \l; \dn". Is
it possible???


No. If you a user connect directly to the database, they can query  
the system catalogs. So even if you somehow disabled the psql  
command, they could still execute something like "SELECT * from  
pg_database".





John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


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

Re: [GENERAL] Views- Advantages and Disadvantages

[Dann Corbit]

> -Original Message-
> From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 09, 2007 12:14 PM
> To: Dann Corbit
> Cc: Ashish Karalkar; Andrej Ricnik-Bay; Ron Johnson; pgsql-
> [EMAIL PROTECTED]
> Subject: Re: [GENERAL] Views- Advantages and Disadvantages
> 
> 
> On May 9, 2007, at 14:02 , Dann Corbit wrote:
> 
> > Views can hide important information from the optimizer (especially
> > index information).
> 
> Really? AIUI, views-at least in PostgreSQL-are implemented using
> PostgreSQL's rule system: the entire query is rewritten to include
> the view query, and the optimizer sees the rewritten query. What the
> optimizer sees is the same as if the view were inlined in the
> original query.

That is a significant achievement, since many database systems do not
have that ability.

---(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] Audit-trail engine: getting the application's layer user_id

[Tilmann Singer]

* Marcelo de Moraes Serpa <[EMAIL PROTECTED]> [20070509 21:14]:
> Just replying to say a big thank you ... 
> I compiled the C extension with the
> code you , did all the necessary logic and finally solved it. Thank you very
> much for your help!

I second that!

I finally settled with the simpler solution which was to create a
temporary table instead of using a global variable in a C function
which seemed reasonably fast enough for us and doesn't introduce the
maintainance dependency on a custom C module.


Til

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Michael Glaesemann]

On May 9, 2007, at 14:02 , Dann Corbit wrote:


Views can hide important information from the optimizer (especially
index information).


Really? AIUI, views—at least in PostgreSQL—are implemented using  
PostgreSQL's rule system: the entire query is rewritten to include  
the view query, and the optimizer sees the rewritten query. What the  
optimizer sees is the same as if the view were inlined in the  
original query.


Michael Glaesemann
grzm seespotcode net



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

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Marcelo de Moraes Serpa]

Hi Manuel,

Just replying to say a big thank you ... I compiled the C extension with the
code you , did all the necessary logic and finally solved it. Thank you very
much for your help!

Thank you also to all the other who helped me!

Marcelo.

On 4/24/07, Manuel Sugawara <[EMAIL PROTECTED]> wrote:


"Marcelo de Moraes Serpa" <[EMAIL PROTECTED]> writes:

> Hey guys,
>
> Mine is an web application - three tier. The app connects to the db
using
> only one user and it has it's own authentication system and doesn't
> rely on the database for user management.

I solved the problem using a C program and keeping all the information
in the database, that means, users, passwords and ``sessions''. Each
time a user opens a session the system register it in a table that
looks like:

auth.session
 Tabla «auth.session»
Columna|Tipo |
Modificadores

---+-+
id| integer | not null default nextval(('
auth.session_sid'::text)::regclass)
skey  | text| not null
agent_id  | integer | not null
host  | text| not null default
'localhost'::text
start_time| timestamp without time zone | not null default now()
end_time  | timestamp without time zone |
su_session_id | integer |
Índices:
«session_pkey» PRIMARY KEY, btree (id)
Restricciones de llave foránea:
«$1» FOREIGN KEY (agent_id) REFERENCES auth.agent(id)
«session_su_session_id_fkey» FOREIGN KEY (su_session_id) REFERENCES
auth."session"(id)

Each time the application gets a connection from the pool it sets the
session id of that user in a static variable (that was the tricky
part) using a function set_session_id and then you can retrieve it
using another function current_session_id (nowadays I think that can
be done without C at all but using the new GUC infrastructure
). So you can put in your log table something like:


  session_id int not null default current_session_id() references
auth.session(id),


Finally before returning the connection to the pool the application
resets the session id of that user using reset_session_id. The code
is:

#include "postgres.h"

#include 
#include 
#include 
#include 
#include "fmgr.h"

static int session_id = 0;
static int session_id_is_set = false;

Datum set_session_id(PG_FUNCTION_ARGS);
Datum current_session_id(PG_FUNCTION_ARGS);
Datum reset_session_id(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(set_session_id);
PG_FUNCTION_INFO_V1(current_session_id);
PG_FUNCTION_INFO_V1(reset_session_id);

Datum
set_session_id(PG_FUNCTION_ARGS) {
session_id = PG_GETARG_INT32(0);
session_id_is_set = true;
PG_RETURN_INT32(session_id);
}

Datum
current_session_id(PG_FUNCTION_ARGS) {
if (! session_id_is_set)
PG_RETURN_NULL();
PG_RETURN_INT32(session_id);
}

Datum
reset_session_id(PG_FUNCTION_ARGS) {
session_id_is_set = false;
PG_RETURN_BOOL(1);
}

Hope that helps.

Regards,
Manuel.

Re: [GENERAL] Views- Advantages and Disadvantages

[Dann Corbit]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Ashish Karalkar
> Sent: Wednesday, May 09, 2007 1:36 AM
> To: Andrej Ricnik-Bay; Ron Johnson
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Views- Advantages and Disadvantages
> 
> Thanks All for your replies,
> 
> But then dont we have any disadvantage of using View???

Views can hide important information from the optimizer (especially
index information).


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

[GENERAL] re Referential Integrity Flash Tutorial

[Lou O'Quin]

I have recommended many, many folks to the referential integrity flash
tutorial that was posted in the old tech docs, whether ot not they were
using PostgreSQL.  Does anyone know where the tutorial was moved to???

the old address was

http://techdocs.postgresql.org/college/002_referentialintegrity/index.php


Thanks

Lou O'Quin



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

Re: [GENERAL] In theory question

[PFC]

This may be a question for -hackers, but I don't like disturbing them
unnecessarily.
I've been having a look at memcached. I would like to ask, is there any
reason that, theoretically, a similar caching system could be built
right into the db serving daemon?
I.e., the hash tables and libevent could sit on top of postmaster as an
optional component caching data on a per-query basis and only hitting
the actual db in the event of a cache miss?


I think this is close to what MySQL's query cache does. The question is  
if this should be the job of the DBMS and not another layer. At least  
the pgmemcache author and I think that it's better done outside the  
DBMS. See http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf  
for the idea.


I have always found MySQL's query cache to be utterly useless.

Think about it this way :

It only works for tables that seldom change.
	It does not work for big tables (like the posts table of a forum) because  
the cache would have to be huge.


	So, the most frequent usage of MySQL's query cache is for dumb  
applications who use, for instance, PHP, store their configuration in  
MySQL, and reload it on each and every page with a SELECT * FROM  
configuration.


	In this case, you save the query time, but you don't save : the roundtrip  
between PHP and the database, extracting query results, building objects  
from them, time spent in ORMs, etc.


	A much better solution is to do your own caching, for instance using  
shared memory in the application server, and then you cache native  
language objects. You not only save the query time, but also all the time  
spent building those objects on every page load.


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

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ilan Volow]

On May 9, 2007, at 4:36 AM, Ashish Karalkar wrote:


Thanks All for your replies,
But then dont we have any disadvantage of using View???

With Reagrds
Ashish...





I once inherited a database that made extensive use of constants in  
views (a la magic numbers) as well as had several instances of  
complex views referencing other complex views. The magic numbers made  
it extremely difficult at first understand what was going on with the  
entire application (both on server and client) because it appeared  
that I was getting values out of thin air that were not coming from  
any tables or the client. The views-referencing-other-views made  
changing a view several layers up in the dependency hierarchy a real  
PITA as I had to completely drop all the views and re-add them.


Views are powerful, but with great power comes great responsibility.  
They can be very easily abused IMHO.


--
Ilan Volow
Implicit code is inherently evil, and here's the reason why:

Re: [GENERAL] CentOS 5, pg8.4.2, could not read time zone file

[Tom Lane]

"Wade Hampton" <[EMAIL PROTECTED]> writes:
> On 5/9/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Anyway, having been burnt before I always wonder about SELinux whenever
>> any strange permission failures turn up on recent RHEL/Fedora systems.

> SELinux is off and there were no avc denied messages in /var/log/messages

Hmph.  Well, then it actually must be a permissions problem on the
timezone file or one of its parent directories ... did you check?

regards, tom lane

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

Re: [GENERAL] In theory question

[Naz Gassiep]

 
>> This is exactly what I was asking about. So my theoretical idea has
>> already been implemented. Now if only *all* my ideas were done for me by
>> the time I came up with them :)
>
> Then you wouldn't be able to eventually patent them ;)
What an un-BSD licensish thing to say :P

---(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] In theory question

[Joshua D. Drake]

Naz Gassiep wrote:

Hannes Dorbath wrote:

I think this is close to what MySQL's query cache does. The question
is if this should be the job of the DBMS and not another layer. At
least the pgmemcache author and I think that it's better done outside
the DBMS. See
http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for the idea.

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)


Then you wouldn't be able to eventually patent them ;)



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




--

  === 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] In theory question

[Naz Gassiep]

Hannes Dorbath wrote:
> I think this is close to what MySQL's query cache does. The question
> is if this should be the job of the DBMS and not another layer. At
> least the pgmemcache author and I think that it's better done outside
> the DBMS. See
> http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for the idea.
This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

---(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] PITR and tar

[Jeff Davis]

On Wed, 2007-05-09 at 11:40 -0500, Jim Nasby wrote:
> Actually, looking at the docs, the problem is with some versions of  
> GNU tar. AFAIK bsdtar is perfectly happy to archive files that have  
> changed from underneath it.
> 

$ tar --version
bsdtar 1.2.53 - libarchive 1.3.1

That fails to create a file in proper gzip format when the files are
concurrently modified.

However, 

$ tar --version
tar (GNU tar) 1.14
Copyright (C) 2004 Free Software Foundation, Inc.
This program comes with NO WARRANTY, to the extent permitted by law.
You may redistribute it under the terms of the GNU General Public
License;
see the file named COPYING for details.
Written by John Gilmore and Jay Fenlason.

That _appears_ to work.

Perhaps FreeBSD users should take notice of this problem. It's certainly
not a postgresql problem, but I know there are a lot of freebsd users
here, and using tar on fast-changing data may be rare outside of
postgresql.

Regards,
Jeff Davis


---(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] CentOS 5, pg8.4.2, could not read time zone file

[Wade Hampton]

The build of rhel src rpm failed due to "This platform is not thread
safe.  Check the file 'config.lg' fo rthe exact reason."

For now I am going back to 8.1.8 that came with CentOS 5.

Thanks,
--
Wade Hampton

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

Re: [GENERAL] CentOS 5, pg8.4.2, could not read time zone file

[Wade Hampton]

On 5/9/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Wade Hampton" <[EMAIL PROTECTED]> writes:
> On my CentOS 5.0 box with the RHEL version of Postgresql 8.4.2, the
> initial initdb step fails with the error

>   "WARNING:  cold not read time zone file "Default" : permission denied.
>   FATAL:  invalid value for parameter "timezone_abbreviations": "Default"

"8.4.2"?  Did this message fall through a time warp?

Oops, 8.2.4, my bad.



Anyway, having been burnt before I always wonder about SELinux whenever
any strange permission failures turn up on recent RHEL/Fedora systems.
Look in /var/log/messages to see if there's an "avc denied" log entry
corresponding to this, or temporarily turn off SELinux with
/usr/sbin/setenforce and see if it works then.

SELinux is off and there were no avc denied messages in /var/log/messages


If it is SELinux preventing the access, you probably need to run
restorecon to fix the SELinux labels on these files.  If it still
doesn't work after that, file a bug report against the selinux policy
module.


I ran strace on it and it dies at the same place each time:

 su postgres -c "strace initdb -D /var/lib/pgsql/data"

 ..
 write(4, "insert OID = 1153 (timestampz_", 116WARNING:  could
not read.
 ... = -1 EPIPE (Broken pipe)

 /etc/localtime is:
   TZif2\0... UTC... UTC0

 locale is en_US.UTF-8

I am downloading the source rpm and building RPMS specifically for
CentOS 5 to see if it helps.

Thanks,
--
Wade Hampton

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

Re: [GENERAL] PITR and tar

[Jim Nasby]

Actually, looking at the docs, the problem is with some versions of  
GNU tar. AFAIK bsdtar is perfectly happy to archive files that have  
changed from underneath it.


On May 9, 2007, at 10:45 AM, Dhaval Shah wrote:


Looks like a problem specific to FreeBSD. I use Centos/postgres 8.2.3
and I do not see that problem at all.

Dhaval

On 5/8/07, Jeff Davis <[EMAIL PROTECTED]> wrote:

On Tue, 2007-05-08 at 13:24 -0400, Merlin Moncure wrote:
> On 5/8/07, Jeff Davis <[EMAIL PROTECTED]> wrote:
> > On Tue, 2007-05-08 at 08:47 +0200, Albe Laurenz wrote:
> > > > The docs recommend using tar to perform a base backup for  
PITR.

> > > >
> > > > Usually, tar reports notices like:
> > > > "tar: Truncated write; file may have grown while being  
archived."

> > >
> > > Did you call pg_start_backup(text) before you started to  
archive?

> > >
> >
> > I was referring to the result of the tar itself being a  
corrupted gzip

> > file (that couldn't be uncompressed with gunzip).
> >
> > I did indeed call pg_start/stop_backup().
>
> is fsync on?
>

Yes. I have a battery-backed cache as well, and there were no power
failures involved.

Regards,
Jeff Davis


---(end of  
broadcast)---

TIP 3: Have you checked our extensive FAQ?

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




--
Dhaval Shah

---(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 3: Have you checked our extensive FAQ?

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

Re: [GENERAL] problem with a conditional statement

[Kirk Wythers]

On May 9, 2007, at 10:41 AM, Erik Jones wrote:



On May 9, 2007, at 10:32 AM, Kirk Wythers wrote:





Here is a more elaborate version, I'm trying to add 'avgsol' to
your original FROM clause:



SELECT CASE WHEN w.station_id = site_near.station_id THEN
w.obs_id ELSE
s.obs_id END AS obs_id,  site_near.station_id, site_near.longname,
w.year, w.doy, w.precip, w.tmin, w.tmax,
--replace missing values (-999) with the monthly average
CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END




FROM site_near
  INNER JOIN solar s ON
(site_near.ref_solar_station_id = s.station_id
 AND site_near.obs_year = s.year)
  INNER JOIN weather w ON
(site_near.ref_weather_station_id = w.station_id
 AND site_near.obs_year = w.year
 AND s.date = w.date)
  INNER JOIN (SELECT month, AVG(par) FROM solar GROUP BY month) AS
avgsol ON
(s.month = avgsol.month)
WHERE ...

Still no claim for correctness.

Does it make more sense now?


Thanks again Laurenz. Hopefully I have nearly figured this out. I  
have a question that indicates to me that I am a little fuzzy on  
one more point.


In the line: "CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END"

I am getting an error that says, "column avgsol.par does not  
exist". I understand that you are creating avgsol as a subselect,  
but I also see the point of the error message that the column .par  
does not exist. If I change avgsol.par to the simple form avgsol  
(to match the subselect " INNER JOIN (SELECT month, AVG(par) FROM  
solar GROUP BY month) AS avgsol ON (s.month = avgsol.month)". Then  
I get an error about CASE types real and record cannot be matched.  
Any final ideas?


The "AVG(par)" should've been aliased.  "AVG(par) as par" would  
work.  As is, the column name returned is just "avg".


Got it! Thankyou!




erik jones <[EMAIL PROTECTED]>
software developer
615-296-0838
emma(r)






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

Re: [GENERAL] Problem with data corruption and psql memory usage

[Tom Lane]

Gerhard Wiesinger <[EMAIL PROTECTED]> writes:
> The only problem I could believe is:
> I'm running the FC7 test packages of postgresql in FC6 and maybe there is 
> a slight glibc library conflict or any other incompatibility.

Hmm, I'd be suspicious of that too.  You'd be well advised to take the FC7
SRPM and rebuild it locally to ensure you don't have any conflicts of
that sort.

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] Problem with data corruption and psql memory usage

[Gerhard Wiesinger]

Hello Tom!

I don't think this is a hardware problem. Machine runs 24/7 for around 4 
years without any problems, daily backup with GBs of data to it, 
uptimes to the next kernel security patch, etc.


The only problem I could believe is:
I'm running the FC7 test packages of postgresql in FC6 and maybe there is 
a slight glibc library conflict or any other incompatibility.


Ciao,
Gerhard

--
http://www.wiesinger.com/


On Wed, 9 May 2007, Tom Lane wrote:


Gerhard Wiesinger <[EMAIL PROTECTED]> writes:

LOG:  could not fsync segment 0 of relation 1663/16386/42726: Input/output
error


[ raised eyebrow... ]  I think your machine is flakier than you believe.
This error is particularly damning, but the general pattern of weird
failures all over the place seems to me to fit the idea of hardware
problems much better than any other explanation.  FC6 and PG 8.2.3 are
both pretty darn stable for most people, so there's *something* wrong
with your installation, and unstable hardware is the first thing that
comes to mind.

regards, tom lane

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

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



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

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

Re: [GENERAL] PITR and tar

[Dhaval Shah]

Looks like a problem specific to FreeBSD. I use Centos/postgres 8.2.3
and I do not see that problem at all.

Dhaval

On 5/8/07, Jeff Davis <[EMAIL PROTECTED]> wrote:

On Tue, 2007-05-08 at 13:24 -0400, Merlin Moncure wrote:
> On 5/8/07, Jeff Davis <[EMAIL PROTECTED]> wrote:
> > On Tue, 2007-05-08 at 08:47 +0200, Albe Laurenz wrote:
> > > > The docs recommend using tar to perform a base backup for PITR.
> > > >
> > > > Usually, tar reports notices like:
> > > > "tar: Truncated write; file may have grown while being archived."
> > >
> > > Did you call pg_start_backup(text) before you started to archive?
> > >
> >
> > I was referring to the result of the tar itself being a corrupted gzip
> > file (that couldn't be uncompressed with gunzip).
> >
> > I did indeed call pg_start/stop_backup().
>
> is fsync on?
>

Yes. I have a battery-backed cache as well, and there were no power
failures involved.

Regards,
Jeff Davis


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

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




--
Dhaval Shah

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

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

Re: [GENERAL] problem with a conditional statement

[Erik Jones]

On May 9, 2007, at 10:32 AM, Kirk Wythers wrote:





Here is a more elaborate version, I'm trying to add 'avgsol' to
your original FROM clause:



SELECT CASE WHEN w.station_id = site_near.station_id THEN
w.obs_id ELSE
s.obs_id END AS obs_id,  site_near.station_id, site_near.longname,
w.year, w.doy, w.precip, w.tmin, w.tmax,
--replace missing values (-999) with the monthly average
CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END




FROM site_near
  INNER JOIN solar s ON
(site_near.ref_solar_station_id = s.station_id
 AND site_near.obs_year = s.year)
  INNER JOIN weather w ON
(site_near.ref_weather_station_id = w.station_id
 AND site_near.obs_year = w.year
 AND s.date = w.date)
  INNER JOIN (SELECT month, AVG(par) FROM solar GROUP BY month) AS
avgsol ON
(s.month = avgsol.month)
WHERE ...

Still no claim for correctness.

Does it make more sense now?


Thanks again Laurenz. Hopefully I have nearly figured this out. I  
have a question that indicates to me that I am a little fuzzy on  
one more point.


In the line: "CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END"

I am getting an error that says, "column avgsol.par does not  
exist". I understand that you are creating avgsol as a subselect,  
but I also see the point of the error message that the column .par  
does not exist. If I change avgsol.par to the simple form avgsol  
(to match the subselect " INNER JOIN (SELECT month, AVG(par) FROM  
solar GROUP BY month) AS avgsol ON (s.month = avgsol.month)". Then  
I get an error about CASE types real and record cannot be matched.  
Any final ideas?


The "AVG(par)" should've been aliased.  "AVG(par) as par" would  
work.  As is, the column name returned is just "avg".


erik jones <[EMAIL PROTECTED]>
software developer
615-296-0838
emma(r)




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

Re: [GENERAL] In theory question

[Tom Lane]

Naz Gassiep <[EMAIL PROTECTED]> writes:
> I.e., the hash tables and libevent could sit on top of postmaster as an
> optional component caching data on a per-query basis and only hitting
> the actual db in the event of a cache miss?

How does the cache know when the database contents change?

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] problem with a conditional statement

[Kirk Wythers]

Here is a more elaborate version, I'm trying to add 'avgsol' to
your original FROM clause:



SELECT CASE WHEN w.station_id = site_near.station_id THEN
w.obs_id ELSE
s.obs_id END AS obs_id,  site_near.station_id, site_near.longname,
w.year, w.doy, w.precip, w.tmin, w.tmax,
--replace missing values (-999) with the monthly average
CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END




FROM site_near
  INNER JOIN solar s ON
(site_near.ref_solar_station_id = s.station_id
 AND site_near.obs_year = s.year)
  INNER JOIN weather w ON
(site_near.ref_weather_station_id = w.station_id
 AND site_near.obs_year = w.year
 AND s.date = w.date)
  INNER JOIN (SELECT month, AVG(par) FROM solar GROUP BY month) AS
avgsol ON
(s.month = avgsol.month)
WHERE ...

Still no claim for correctness.

Does it make more sense now?


Thanks again Laurenz. Hopefully I have nearly figured this out. I  
have a question that indicates to me that I am a little fuzzy on one  
more point.


In the line: "CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END"

I am getting an error that says, "column avgsol.par does not exist".  
I understand that you are creating avgsol as a subselect, but I also  
see the point of the error message that the column .par does not  
exist. If I change avgsol.par to the simple form avgsol (to match the  
subselect " INNER JOIN (SELECT month, AVG(par) FROM solar GROUP BY  
month) AS avgsol ON (s.month = avgsol.month)". Then I get an error  
about CASE types real and record cannot be matched. Any final ideas?




Yours,
Laurenz Albe



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

  http://archives.postgresql.org/

Re: [GENERAL] In theory question

[Erik Jones]

On May 9, 2007, at 10:22 AM, Hannes Dorbath wrote:


On 09.05.2007 16:13, Naz Gassiep wrote:

This may be a question for -hackers, but I don't like disturbing them
unnecessarily.
I've been having a look at memcached. I would like to ask, is  
there any

reason that, theoretically, a similar caching system could be built
right into the db serving daemon?
I.e., the hash tables and libevent could sit on top of postmaster  
as an

optional component caching data on a per-query basis and only hitting
the actual db in the event of a cache miss?


I think this is close to what MySQL's query cache does. The  
question is if this should be the job of the DBMS and not another  
layer. At least the pgmemcache author and I think that it's better  
done outside the DBMS. See http://people.FreeBSD.org/~seanc/ 
pgmemcache/pgmemcache.pdf for the idea.


I just read through that pdf.  How does implementing a memcached  
system with table triggers qualify as outside the database?


erik jones <[EMAIL PROTECTED]>
software developer
615-296-0838
emma(r)




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

Re: [GENERAL] CentOS 5, pg8.4.2, could not read time zone file

[Tom Lane]

"Wade Hampton" <[EMAIL PROTECTED]> writes:
> On my CentOS 5.0 box with the RHEL version of Postgresql 8.4.2, the
> initial initdb step fails with the error

>   "WARNING:  cold not read time zone file "Default" : permission denied.
>   FATAL:  invalid value for parameter "timezone_abbreviations": "Default"

"8.4.2"?  Did this message fall through a time warp?

Anyway, having been burnt before I always wonder about SELinux whenever
any strange permission failures turn up on recent RHEL/Fedora systems.
Look in /var/log/messages to see if there's an "avc denied" log entry
corresponding to this, or temporarily turn off SELinux with
/usr/sbin/setenforce and see if it works then.

If it is SELinux preventing the access, you probably need to run
restorecon to fix the SELinux labels on these files.  If it still
doesn't work after that, file a bug report against the selinux policy
module.

regards, tom lane

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

Re: [GENERAL] In theory question

[Hannes Dorbath]

On 09.05.2007 16:13, Naz Gassiep wrote:

This may be a question for -hackers, but I don't like disturbing them
unnecessarily.

I've been having a look at memcached. I would like to ask, is there any
reason that, theoretically, a similar caching system could be built
right into the db serving daemon?

I.e., the hash tables and libevent could sit on top of postmaster as an
optional component caching data on a per-query basis and only hitting
the actual db in the event of a cache miss?


I think this is close to what MySQL's query cache does. The question is 
if this should be the job of the DBMS and not another layer. At least 
the pgmemcache author and I think that it's better done outside the 
DBMS. See http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for 
the idea.



--
Regards,
Hannes Dorbath

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

Re: [GENERAL] CentOS 5, pg8.4.2, could not read time zone file

[Hannes Dorbath]

On 09.05.2007 16:51, Wade Hampton wrote:

On my CentOS 5.0 box with the RHEL version of Postgresql 8.4.2, the


Hey, that's what I call bleeding edge ;)


initial initdb step fails with the error

 "WARNING:  cold not read time zone file "Default" : permission denied.
 FATAL:  invalid value for parameter "timezone_abbreviations": "Default"


strace initdb -D /foo/bar

see what exactly it tries to read and check if it has permissions to do so.


--
Regards,
Hannes Dorbath

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

Re: [GENERAL] PG on Debian 4.0.x ?

[Alvaro Herrera]

Ron Johnson wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 05/09/07 09:13, Alvaro Herrera wrote:
> [snip]
> > 
> > I tried it here and it didn't work because it only has packages for
> > i386, and my system is amd64.  However, I got it by source with
> 
> Your build environment is somehow broken.
> 
> The same deb-src should build locally-hostable binaries, since
> that's what your local gcc creates.

It's not broken, you just misread what I said.  I _was_ able to create
the binary packages.  The binary repo for pgadmin3 only has i386 binaries.

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

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

Re: [GENERAL] PG on Debian 4.0.x ?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/09/07 09:13, Alvaro Herrera wrote:
[snip]
> 
> I tried it here and it didn't work because it only has packages for
> i386, and my system is amd64.  However, I got it by source with

Your build environment is somehow broken.

The same deb-src should build locally-hostable binaries, since
that's what your local gcc creates.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

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

iD8DBQFGQeF9S9HxQb37XmcRAsAIAJ0Rdumb3wAIwKNxUCXxrS72svB2ugCffTs1
NEuipADHofRIkJFPAcaQoPI=
=IIsN
-END PGP SIGNATURE-

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

Re: [GENERAL] Problem with data corruption and psql memory usage

[Tom Lane]

Gerhard Wiesinger <[EMAIL PROTECTED]> writes:
> LOG:  could not fsync segment 0 of relation 1663/16386/42726: Input/output 
> error

[ raised eyebrow... ]  I think your machine is flakier than you believe.
This error is particularly damning, but the general pattern of weird
failures all over the place seems to me to fit the idea of hardware
problems much better than any other explanation.  FC6 and PG 8.2.3 are
both pretty darn stable for most people, so there's *something* wrong
with your installation, and unstable hardware is the first thing that
comes to mind.

regards, tom lane

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

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

[GENERAL] CentOS 5, pg8.4.2, could not read time zone file

[Wade Hampton]

On my CentOS 5.0 box with the RHEL version of Postgresql 8.4.2, the
initial initdb step fails with the error

 "WARNING:  cold not read time zone file "Default" : permission denied.
 FATAL:  invalid value for parameter "timezone_abbreviations": "Default"

A search of the mail list and google showed this appearing on Windows,
but I am on Linux.
Anyone have any workaround?

--
Wade Hampton

---(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] In theory question

[Michael Glaesemann]

On May 9, 2007, at 9:13 , Naz Gassiep wrote:

I've been having a look at memcached. I would like to ask, is there  
any

reason that, theoretically, a similar caching system could be built
right into the db serving daemon?


This is all a bit above my head, but have you looked at pgmemcached?

http://people.freebsd.org/~seanc/pgmemcache/

Michael Glaesemann
grzm seespotcode net



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

Re: [GENERAL] are foreign keys realized as indexes?

[Tom Lane]

Felix Kater <[EMAIL PROTECTED]> writes:
> On Tue, 8 May 2007 15:54:08 +0200
> Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
>> A unique index is not a "substitute" for a unique constraint, they're
>> exactly the same thing.

> Yes. For this reason I didn't have to implement *both* 'unique
> constraints' *and* 'unique indices' in my pg interface.

If you are trying to get away with a dumbed-down subset of SQL, be
prepared for people to refuse to use your tool ;-).

You have to support the unique-constraint syntax because the SQL spec
says so (and people are used to it), and you have to support the
create-index syntax because it gives access to functionality not
available through the constraint syntax.  Unique indexes on expressions
for instance.

regards, tom lane

---(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] Dangers of fsync = off

[Scott Ribe]

> I still wouldn't trust Slony with fsync off.  Another scenario would be
> the Slony trigger writes a change to the Slony DB, the db crashes before
> it gets committed to disk.  When the DB is started, no errors prevent
> startup, but that transaction is lost.

I'm not sure, but I think the questioner was proposing a policy of "if it
crashes, we go to the standby, no attempt at recovery, ever", and I think
that would be safe.

And, personally, given my experience with pg, I think that's reasonable.
Because the day I see pg crash I'm going to assume I have a hardware problem
;-)

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



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

   http://archives.postgresql.org/

Re: [GENERAL] Views- Advantages and Disadvantages

[Merlin Moncure]

On 5/9/07, Ashish Karalkar <[EMAIL PROTECTED]> wrote:

Thanks Brent for your replay,


What about the Disadvantages, Performance issues [of views]


Views are pretty much neutral from a performance perspective.  There
are certain small considerations here and there to think about but you
should really be focusing on how they impact your development and
design strategies.

Probably there are two disadvantages of using views.   The first is
that the introduce dependencies on your tables so that whenever you
need to certain things to your table you have to drop the view, do the
thing (like drop a column) and re-create the view.   Second issue is
that views are not updatable without the introduction of rules which
are extra dependencies and can be tricky to write.

Now the first problem is not really a problem...in fact, views are a
great way to tell the dba what applications are doing and gives him a
chance to introduce application compatibility if the table structures
change.  So I count this as a feature of using views although it will
feel like extra work to do, especially in the beginning.  At some
point, views (especially trivial ones) in PostgreSQL will become
automatically updatable which will mitigate the impact of the second
disadvantage.  If and when that happens,

IMNSHO, Views are a critical foundation to good development.  Complex
queries drifting around in various applications is A Bad Thing, and
views allow you to control this and provide much better encapsulation
of the database and exercise control over what's going on.  Many
application developers are uncomfortable with the idea of views (and
other high level database features like functions) but when used
properly, the benefits they can bring to your development and
maintenance can be really dramatic.  Use them.

merlin

---(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] PG on Debian 4.0.x ?

[Alvaro Herrera]

Dimitri Fontaine wrote:
> Le mardi 08 mai 2007, Andreas a écrit :
> > Do you know where I find PG 8.2.4 and pgAdmin 1.6.3 binaries for Debian
> > 4.0.x ?
> 
> For server-side debian stable, you can build yourself the package by simply 
> following those steps :
>  1. have your deb-src line (from /etc/apt/sources.list) point to sid
>  2. apt-get update
>  3. apt-get build-dep postgresql-8.2
>  4. apt-get source -b postgresql-8.2
> 
> Then install postgresql-common package, for debian postgresql scripts 
> (as /etc/init.d/postgresql-8.2 and /usr/bin/{psql,pg_dump,...}).
>  5. apt-get install postgresql-common
> 
> Then you install the wanted .deb files with dpkg -i, including 
> libpq5_8.2*deb, 
> all in a single command line:
>  6. dpkg -i ...
> 
> On the client side, simply add this following source :
> # pgadmin3 1.6.x
> deb ftp://ftp3.fr.postgresql.org//pub/postgresql/pgadmin3/release/debian 
> testing pgadmin
> 
> Then apt-get update and apt-get install pgadmin3.

I tried it here and it didn't work because it only has packages for
i386, and my system is amd64.  However, I got it by source with

deb-src ftp://ftp3.fr.postgresql.org//pub/postgresql/pgadmin3/release/debian 
testing pgadmin
aptitude update
apt-get build-dep pgadmin3
apt-get source -b pgadmin3
dpkg -i pgadmin...deb

Took about three minutes of my time, a bit more of machine time.
It automatically downloaded wxWidgets, configured and compiled
everything and I got pgadmin installed on my system for the first time.
Yay!  Thanks Dimitri.

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

[GENERAL] In theory question

[Naz Gassiep]

This may be a question for -hackers, but I don't like disturbing them
unnecessarily.

I've been having a look at memcached. I would like to ask, is there any
reason that, theoretically, a similar caching system could be built
right into the db serving daemon?

I.e., the hash tables and libevent could sit on top of postmaster as an
optional component caching data on a per-query basis and only hitting
the actual db in the event of a cache miss?

Such a mechanism could be a) transparent to any and all APIs accessing
the back end thus instantly providing the benefits of caching to all
apps transparently, and b) assist with replication by providing a way
for a setup to have n serving daemons (effectively db caches) on
different machines accessing 

Re: [GENERAL] Dangers of fsync = off

[Bill Moran]

In response to Csaba Nagy <[EMAIL PROTECTED]>:

> > [snip] Take the example of a query "UPDATE tablename SET x = x + 1".
> > When this query is erroneously issued twice, data corruption will occur.
> 
> Huh ? I thought slony is replicating data, not queries... what on the
> master is "UPDATE tablename SET x = x + 1" will translate to "UPDATE
> tablename SET x = new_value" on the slave, where new_value equals that x
> + 1. That's why slony is working well even if you do "UPDATE tablename
> SET x = now()".

True.  My mistake.

I still wouldn't trust Slony with fsync off.  Another scenario would be
the Slony trigger writes a change to the Slony DB, the db crashes before
it gets committed to disk.  When the DB is started, no errors prevent
startup, but that transaction is lost.

I mean, you have to weight all these possibilities against your tolerance
for data loss.  If you're a bank, none of this is acceptable.  If you're
MySpace, who f*cking cares if you lose data (I saw an article where the
CIO of MySpace admitted that was their policy -- must be nice to have a
job where nobody cares if you do it wrong!)

-- 
Bill Moran
http://www.potentialtech.com

---(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] Dangers of fsync = off

[Csaba Nagy]

> [snip] Take the example of a query "UPDATE tablename SET x = x + 1".
> When this query is erroneously issued twice, data corruption will occur.

Huh ? I thought slony is replicating data, not queries... what on the
master is "UPDATE tablename SET x = x + 1" will translate to "UPDATE
tablename SET x = new_value" on the slave, where new_value equals that x
+ 1. That's why slony is working well even if you do "UPDATE tablename
SET x = now()".

Cheers,
Csaba.



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

Re: [GENERAL] Issue with database Postgresql :(

[Peter Eisentraut]

Am Dienstag, 8. Mai 2007 19:20 schrieb Gerard M:
> Whenever I try to save a word containing "special" characters in it
> (for example áéíóú) I get the following django error:
> "invalid byte sequence for encoding "UTF8": 0xe92020 HINT: This error
> can also happen if the byte sequence does not match the encoding
> expected by the server, which is controlled by "client_encoding"."
> I know that I need to set something to my database/table but I dont
> know where or how to achieve this,

Set client_encoding to the encoding that your client application is really 
producing.

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

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

Re: [GENERAL] Dangers of fsync = off

[Bill Moran]

In response to Joel Dice <[EMAIL PROTECTED]>:

> Thanks for your response, Andrew.
> 
> On Tue, 8 May 2007, Andrew Sullivan wrote:
> 
> > On Fri, May 04, 2007 at 08:54:10AM -0600, Joel Dice wrote:
> >>
> >> My next question is this: what are the dangers of turning fsync off in the
> >> context of a high-availablilty cluster using asynchronous replication?
> >
> > My real question is why you want to turn it off.  If you're using a
> > battery-backed cache on your disk controller, then fsync ought to be
> > pretty close to free.  Are you sure that turning it off will deliver
> > the benefit you think it will?
> 
> You may very well be right.  I tend to think in terms of software 
> solutions, but a hardware solution may be most appropriate here.  In any 
> case, I'm not at all sure this will bring a significant peformance 
> improvement.  I just want to understand the implications before I start 
> fiddling; if fsync=off is dangerous, it doesn't matter what the 
> performance benefits may be.
> 
> >> on Y.  Thus, database corruption on X is irrelevant since our first step
> >> is to drop them.
> >
> > Not if the corruption introduces problems for replication, which is
> > indeed possible.
> 
> That's exactly what I want to understand.  How, exactly, is this possible? 
> If the danger of fsync is that it may leave the on-disk state of the 
> database in an inconsistent state after a crash, it would not seem to have 
> any implications for activity occurring prior to the crash.  In 
> particular, a trigger-based replication system would seem to be immune.

If you mean Slony, no.  It's not immune.  Slony maintains its state in
tables in the database.  If fsync is off, there's no guarantee that Slony's
state information is sane, which means replication is not guaranteed to be
or do anything.

> In other words, while there may be ways the master could cause corruption 
> on the slave, I don't see how they could be related to the fsync setting.

Specifically, I can imagine a system crashing, then _seeming_ to restart
properly, but Slony starts re-replicating transactions that have already
been replicated once because the ACKs were never written to disk on the
master.  Take the example of a query "UPDATE tablename SET x = x + 1".
When this query is erroneously issued twice, data corruption will occur.

Other scenarios may be possible.

-- 
Bill Moran
http://www.potentialtech.com

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

Re: [GENERAL] Views- Advantages and Disadvantages

[woodb]

> On 5/9/07, Ashish Karalkar <[EMAIL PROTECTED]> wrote:
>
> Hello All,
> Can anybody please point me to Advantages and Disadvantages
> of using view

Hi Ashish,

There are several, but they are generally about implementing a more user
friendly database from a well normalised structure.


You can use views to do any or all of:

 act as a filter on fields or records which can be accessed by given users,

 act as a stored query to avoid writing a commonly used SQL,

 aggregate normalised tables into a single virtual table, so that access
 does not require as complex a join, thus it can hide the complexity of the
 database structure from applications or users.

An example use is to have a view which provides only the current version
of data from a database which stores multiple versions of the data.

Cheers,

  Brent Wood



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

[GENERAL] Issue with database Postgresql :(

[Gerard M]

Hello dear postgres community, I'm having a bad time with an issue
that I haven't been able to solve with my database, the problem is
this:
Whenever I try to save a word containing "special" characters in it
(for example áéíóú) I get the following django error:
"invalid byte sequence for encoding "UTF8": 0xe92020 HINT: This error
can also happen if the byte sequence does not match the encoding
expected by the server, which is controlled by "client_encoding"."
I know that I need to set something to my database/table but I dont
know where or how to achieve this, when I type" show;" at the
postgresql shell it tells me that my database is UTF-8 encoding.

I solved a problem like this in MySQL with the following commands:

alter database  character set utf8 collate utf8_general_ci;
alter table  convert to character set utf8 collate
utf8_general_ci;

I dont know if something like this exists in postgresql, or if there
is something else I can do or I should be doing, thanks for your help,
and your time for reading this post.


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

[GENERAL] User restrictions

[ebmb]

 Hi all,
 how can I make user restrictions to commands like "\du; \l; \dn". Is
it possible???

 Thanks in advance!

 EBMB.


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

[GENERAL] Problem with data corruption and psql memory usage

[Gerhard Wiesinger]

Hello!

I'm new to Postgresql and I did make some import with about 2.8 
Mio with normal insert commands.


Config was (difference from default config):
listen_addresses = '*'
temp_buffers = 20MB# min 800kB
work_mem = 20MB# min 64kB
maintenance_work_mem = 32MB# min 1MB
fsync = off# turns forced synchronization on or off
full_page_writes = off
wal_buffers = 20MB

It crashed with a core dump (ulimit -c 0):
LOG:  server process (PID 12720) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server 
process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server proc

ess exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and 
repeat your command.
WARNING:  terminating connection because of crash of another server 
process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server proc

ess exited abnormally and possibly corrupted shared memory.

Afterwards I got the following error messages:
WARNING:  index "table_pkey" contains 2572948 row versions, but 
table contains 2572949 row versions

HINT:  Rebuild the index with REINDEX.

LOG:  server process (PID 13794) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server 
process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server proc

ess exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and 
repeat your command.


LOG:  could not fsync segment 0 of relation 1663/16386/42726: Input/output 
error

ERROR:  storage sync failed on magnetic disk: Input/output error

ERROR:  could not access status of transaction 808464434
DETAIL:  Could not open file "pg_clog/0303": No such file or directory.

Afterwards I got:
ERROR:  could not access status of transaction 5526085

There were also some coredumps afterwards where I have a stack trace:
#0  0x0807d241 in heap_deform_tuple ()
#1  0x08095b8c in toast_delete ()
#2  0x0809432e in heap_delete ()
#3  0x0814bfa4 in ExecutorRun ()
#4  0x081d7ece in FreeQueryDesc ()
#5  0x081d80c1 in FreeQueryDesc ()
#6  0x081d8979 in PortalRun ()
#7  0x081d4480 in pg_parse_query ()
#8  0x081d5a57 in PostgresMain ()
#9  0x081ad4fe in ClosePostmasterPorts ()
#10 0x081ae307 in PostmasterMain ()
#11 0x0816dec0 in main ()

So my questions are:
1.) Are my settings to aggresive (fsync=off, full_page_writes=off)?
2.) Should PostgreSQL also recover with these 2 options enabled on a core 
dump or is data corruption normally with these settings?

3.) Any ideas for the reason of coredumps?

Write access was only from one session at a time. I only did select 
count(*) from table from other sessions.


Afterwards I cleaned up the tables, pg_dumpall/restore session, 
initdb and disabled these 2 settings and everything went fine.


I also had a problem with psql:
psql < file.sql
=> psql took around 2GB virtual memory with heavy swapping. After 
Ctrl-C, restarting, it worked well. Any ideas?


Machine is stable so I would say that a hardware failure is not the 
problem.


Postgresql version is 8.2.3 on FC6

Thank you for the answer.

Ciao,
Gerhard

--
http://www.wiesinger.com/

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

Re: [GENERAL] PostgreSql embedded available?

[Austin Winstanley]

Thanks for the replies and help everyone...



On 5/8/07, Rich Shepard <[EMAIL PROTECTED]> wrote:


On Tue, 8 May 2007, Scott Marlowe wrote:

>> Is there a version of PostgreSql that can be embedded with an
>> application?

> Nope, and it's not real likely to happen.  Take a look at sqllite.

   Yes, SQLite (http://www.sqlite.org/) is the way to go for embedded
RDBMS.
Syntax is close to that of postgres, and it's available for use in any
application. Great product when you need to incorporate an invisible data
storage facility.

Rich

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

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





--
Thanks,
Austin Winstanley
Tradelogic Software Group

Re: [GENERAL] Dangers of fsync = off

[Joel Dice]

Thanks for your response, Andrew.

On Tue, 8 May 2007, Andrew Sullivan wrote:


On Fri, May 04, 2007 at 08:54:10AM -0600, Joel Dice wrote:


My next question is this: what are the dangers of turning fsync off in the
context of a high-availablilty cluster using asynchronous replication?


My real question is why you want to turn it off.  If you're using a
battery-backed cache on your disk controller, then fsync ought to be
pretty close to free.  Are you sure that turning it off will deliver
the benefit you think it will?


You may very well be right.  I tend to think in terms of software 
solutions, but a hardware solution may be most appropriate here.  In any 
case, I'm not at all sure this will bring a significant peformance 
improvement.  I just want to understand the implications before I start 
fiddling; if fsync=off is dangerous, it doesn't matter what the 
performance benefits may be.



on Y.  Thus, database corruption on X is irrelevant since our first step
is to drop them.


Not if the corruption introduces problems for replication, which is
indeed possible.


That's exactly what I want to understand.  How, exactly, is this possible? 
If the danger of fsync is that it may leave the on-disk state of the 
database in an inconsistent state after a crash, it would not seem to have 
any implications for activity occurring prior to the crash.  In 
particular, a trigger-based replication system would seem to be immune.


In other words, while there may be ways the master could cause corruption 
on the slave, I don't see how they could be related to the fsync setting.


 - Joel

---(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] Solaris Postgresql 8.1.8 vs Postgresql 8.2.4

[Simon Smith]

I am planning to set up a new solaris 10 sparc server with a postgresql 
database.

It looks like solaris 10 comes with version 8.1.8 of postgres.

Is there any benefit in using the 8.1.8 included solaris version over the 
current release.

The sun site mentions several enhancement to the solaris version. Do these 
enhancements outweigh 
the features and bug fixes of newer postgres releases.

Thanks,
Simon




 

No need to miss a message. Get email on-the-go 
with Yahoo! Mail for Mobile. Get started.
http://mobile.yahoo.com/mail 

Re: [GENERAL] PG on Debian 4.0.x ?

[Dimitri Fontaine]

Le mardi 08 mai 2007, Andreas a écrit :
> Do you know where I find PG 8.2.4 and pgAdmin 1.6.3 binaries for Debian
> 4.0.x ?

For server-side debian stable, you can build yourself the package by simply 
following those steps :
 1. have your deb-src line (from /etc/apt/sources.list) point to sid
 2. apt-get update
 3. apt-get build-dep postgresql-8.2
 4. apt-get source -b postgresql-8.2

Then install postgresql-common package, for debian postgresql scripts 
(as /etc/init.d/postgresql-8.2 and /usr/bin/{psql,pg_dump,...}).
 5. apt-get install postgresql-common

Then you install the wanted .deb files with dpkg -i, including libpq5_8.2*deb, 
all in a single command line:
 6. dpkg -i ...

On the client side, simply add this following source :
# pgadmin3 1.6.x
deb ftp://ftp3.fr.postgresql.org//pub/postgresql/pgadmin3/release/debian 
testing pgadmin

Then apt-get update and apt-get install pgadmin3.

Regards,
-- 
dim

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

   http://archives.postgresql.org/

Re: [GENERAL] typical schema for a forum?

[PFC]

	I have written a little PHP+postgres forum for benchmarking purposes, to  
see how fast postgres could go.
	It has basic forum features, like forums (duh), topics, posting,  
pagination, watching topics, topic & post count, display newest topic and  
post in topic & forum pages, templates, topics pagination in O(1) not  
O(number of pages), etc.
	I also wrote a companion benchmark script which tortures the server with  
HTTP requests, viewing and posting.


	it answered my "how fast can it go" question : more than 500 dynamic  
pages/second on a Core 2. Most of the load is PHP : postgres could handle  
much, much more than this.


	If people are interested in this as a benchmark, I can write some docs  
(and make a MySQL version).

If you want to use it as a base for your forum, you're welcome.

Who wants the source ?

On Wed, 09 May 2007 10:49:17 +0200, Louis-David Mitterrand  
<[EMAIL PROTECTED]> wrote:



Hi,

I'm trying to implement a forum with mason and postgresql. What is the
typical database schema of a forum (threaded or flat) application?

Thanks,

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




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

Re: [GENERAL] PG on Debian 4.0.x ?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/09/07 04:33, Hannes Dorbath wrote:
> On 09.05.2007 10:43, Peter Eisentraut wrote:
>> Right now you can't, at least not from official or semiofficial
>> sources.  I expect in a few weeks time, backports will show up on
>> backports.org.
> 
> .. and this is what people consider `stable' then? Hacked versions of
> applications somehow made to work with outdated libs :)

Despite your smiley emoticon, you seem not to understand much about
Debian.

The people who upload packages to backports.org are typically the
Maintainers themselves, and those who aren't take the current SRC
DEBs and see if they build against the old libraries.

"Old" libraries that are continuously updated with security patches.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

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

iD8DBQFGQZmJS9HxQb37XmcRAkryAJ9qM36A6p3nb0nHhhPxgehD4zM/nACcDg5I
Yjtm9G3SCDuI7IqsDArD8fE=
=1Az/
-END PGP SIGNATURE-

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

Re: [GENERAL] backup and restore

[anhtin]

thanks reply for me
but have propblem:
the function pg_start_backup()
i dont run this function
Can u send me example is a procedure or function can backup and restore for
me 

-- 
View this message in context: 
http://www.nabble.com/backup-and-restore-tf3714247.html#a10391372
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] are foreign keys realized as indexes?

[Felix Kater]

On Tue, 8 May 2007 15:54:08 +0200
Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:

> A unique index is not a "substitute" for a unique constraint, they're
> exactly the same thing. If you drop your constraint and create a
> unique index, you're back where you started. You neither added nor
> removed anything.

Yes. For this reason I didn't have to implement *both* 'unique
constraints' *and* 'unique indices' in my pg interface.


> On a certain level foreign keys are just triggers, specially coded to
> do the work. Yes, you could write your own triggers to do exactly the
> same thing, but why bother, when someone has written them for you and
> made nice syntax to use them?

My question simply was if I could save coding time... like with 'unique
constaints' and 'indeces', see above. However, for what I have learned
now, 'foreign keys' can *not* be substituted by indeces, so I have to
implement them.

Thanks again.

Felix

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

   http://archives.postgresql.org/

Re: [GENERAL] pg_contraint: 'action code' ?

[Felix Kater]

On Tue, 08 May 2007 10:03:24 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:

>  * the FKCONSTR_MATCH_xxx constants defined in parsenodes.h.

True! ;-)
Thank you so much.

Felix

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

Re: [GENERAL] PG on Debian 4.0.x ?

[Hannes Dorbath]

On 09.05.2007 10:43, Peter Eisentraut wrote:
Right now you can't, at least not from official or semiofficial sources.  I 
expect in a few weeks time, backports will show up on backports.org.


.. and this is what people consider `stable' then? Hacked versions of 
applications somehow made to work with outdated libs :)



--
Regards,
Hannes Dorbath

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

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

Re: [GENERAL] backup and restore

[Hannes Dorbath]

On 09.05.2007 09:45, anhtin wrote:

hi all
I am developing web site use database is PostGres and now i must build
function backup and Restore database on Web
My web develop on framework 2.0(asp.net 2.0)
I know have two file on forder bin use backup and restore is: pg_restore.exe
, pg_dump.exe
I have plan is write function or procedures on Database can backup and
restore database.
How can i write this function ???
Some body show for me?


Well, a good start might be to take a look at the manual of the 
application in question?


See http://www.postgresql.org/docs/current/static/backup.html

--
Regards,
Hannes Dorbath

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

  http://archives.postgresql.org/

[GENERAL] typical schema for a forum?

[Louis-David Mitterrand]

Hi,

I'm trying to implement a forum with mason and postgresql. What is the 
typical database schema of a forum (threaded or flat) application?

Thanks,

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/09/07 03:48, Ashish Karalkar wrote:
> Ok. That is on insert update delete part.
> ant disadvantage on select part?

Performance-wise?  No.

Both an advantage and disadvantage of views is that it codifies
certain queries in the database.

P.S. - top-posting is rude.

> - Original Message - From: "Ron Johnson" <[EMAIL PROTECTED]>
> To: 
> Sent: Wednesday, May 09, 2007 2:18 PM
> Subject: Re: [GENERAL] Views- Advantages and Disadvantages
> 
> 
> On 05/09/07 03:36, Ashish Karalkar wrote:
 Thanks All for your replies,
 But then dont we have any disadvantage of using View???
> 
> You can't insert into multi-table views.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

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

iD8DBQFGQY5QS9HxQb37XmcRAmgZAJ9LiKaJHwsPlTnZi9UpbiqEANpfdACaAqeS
KtJSyTH7FcWp4JOna9BqNkc=
=EBCc
-END PGP SIGNATURE-

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ashish Karalkar]

Ok. That is on insert update delete part.
ant disadvantage on select part?

With Regards
Ashish...


- Original Message - 
From: "Ron Johnson" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, May 09, 2007 2:18 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages



-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/09/07 03:36, Ashish Karalkar wrote:

Thanks All for your replies,
But then dont we have any disadvantage of using View???


You can't insert into multi-table views.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

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

iD8DBQFGQYrNS9HxQb37XmcRAs+uAJ9cVMufkVvGiAFxS6saK0E0B8jOFwCfYYVP
tHQ/Pf7jbmuy7IwdQkneRiU=
=P0t3
-END PGP SIGNATURE-

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


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

  http://archives.postgresql.org/

Re: [GENERAL] Views- Advantages and Disadvantages

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/09/07 03:36, Ashish Karalkar wrote:
> Thanks All for your replies,
> But then dont we have any disadvantage of using View???

You can't insert into multi-table views.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

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

iD8DBQFGQYrNS9HxQb37XmcRAs+uAJ9cVMufkVvGiAFxS6saK0E0B8jOFwCfYYVP
tHQ/Pf7jbmuy7IwdQkneRiU=
=P0t3
-END PGP SIGNATURE-

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

Re: [GENERAL] PG on Debian 4.0.x ?

[Peter Eisentraut]

Am Dienstag, 8. Mai 2007 18:09 schrieb Andreas:
> Do you know where I find PG 8.2.4 and pgAdmin 1.6.3 binaries for Debian
> 4.0.x ?

Right now you can't, at least not from official or semiofficial sources.  I 
expect in a few weeks time, backports will show up on backports.org.

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

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

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ragnar]

On mið, 2007-05-09 at 12:46 +0530, Ashish Karalkar wrote:

> I have found out some of them n are as follows, but  I want more reasons 
> for not using views . I only got one

> 
>  Disadvantages:

> 1)  Performance : If a view is defined by complex multitable query,then 
> simple query against that view becomes a coplecated join, and it may take a 
> long time to complete
> 

2) UPDATES on a view are more tricky.

gnari



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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ashish Karalkar]

Thanks All for your replies, 


But then dont we have any disadvantage of using View???

With Reagrds
Ashish...




- Original Message - 
From: "Andrej Ricnik-Bay" <[EMAIL PROTECTED]>

To: "Ron Johnson" <[EMAIL PROTECTED]>
Cc: 
Sent: Wednesday, May 09, 2007 2:03 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages



On 5/9/07, Ron Johnson <[EMAIL PROTECTED]> wrote:

> 1)  Performance : If a view is defined by complex multitable
> query,then simple query against that view becomes a coplecated
> join, and it may take a long time to complete
I don't see that as relevant, since we know which objects are tables
and which are views.  But maybe that's just our site.

Plus, to retrieve the same information w/o the view he'd
still have to have the same multi-table query which would
a) take as long to run and b) take much longer to type :)


Cheers,
Andrej

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


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

  http://archives.postgresql.org/

Re: [GENERAL] Views- Advantages and Disadvantages

[Andrej Ricnik-Bay]

On 5/9/07, Ron Johnson <[EMAIL PROTECTED]> wrote:

> 1)  Performance : If a view is defined by complex multitable
> query,then simple query against that view becomes a coplecated
> join, and it may take a long time to complete
I don't see that as relevant, since we know which objects are tables
and which are views.  But maybe that's just our site.

Plus, to retrieve the same information w/o the view he'd
still have to have the same multi-table query which would
a) take as long to run and b) take much longer to type :)


Cheers,
Andrej

---(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] Views- Advantages and Disadvantages

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/09/07 02:16, Ashish Karalkar wrote:
[snip]
> Disadvantages:
> 
> 
> 
> 1)  Performance : If a view is defined by complex multitable 
> query,then simple query against that view becomes a coplecated
> join, and it may take a long time to complete

I don't see that as relevant, since we know which objects are tables
and which are views.  But maybe that's just our site.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

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

iD8DBQFGQYL9S9HxQb37XmcRAqhIAKDTiOQpvifDk5nVU87MvGOjLqdV8wCffjf5
3lzftdxJzUDVhNaD9GME3VQ=
=51Vx
-END PGP SIGNATURE-

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ashish Karalkar]

Thanks Brent for your replay,


What about the Disadvantages, Performance issues?


With Regards
Ashish...





- Original Message - 
From: <[EMAIL PROTECTED]>

To: "Postgres General" 
Cc: "Ashish Karalkar" <[EMAIL PROTECTED]>
Sent: Wednesday, May 09, 2007 1:24 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages



On 5/9/07, Ashish Karalkar <[EMAIL PROTECTED]> wrote:

Hello All,
Can anybody please point me to Advantages and Disadvantages
of using view


Hi Ashish,

There are several, but they are generally about implementing a more user
friendly database from a well normalised structure.


You can use views to do any or all of:

act as a filter on fields or records which can be accessed by given users,

act as a stored query to avoid writing a commonly used SQL,

aggregate normalised tables into a single virtual table, so that access
does not require as complex a join, thus it can hide the complexity of the
database structure from applications or users.

An example use is to have a view which provides only the current version
of data from a database which stores multiple versions of the data.

Cheers,

 Brent Wood




- Original Message - 
From: "Ashish Karalkar" <[EMAIL PROTECTED]>

To: "Andrej Ricnik-Bay" <[EMAIL PROTECTED]>
Cc: "Postgres General" 
Sent: Wednesday, May 09, 2007 12:46 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages



Thanks Andrej
for your replay

I have found out some of them n are as follows, but  I want more reasons
for not using views . I only got one

Advantages:

 1)  Permission to user can be given to access the database only
through view containing specific data the user is authorized to see

 2)   View  convert multiple table queries into single table
queries

3)   Views give personalized view

4)   Views provide consistent unchanged image even if the underlying
table columns changed.



Disadvantages:



1)  Performance : If a view is defined by complex multitable 
query,then
simple query against that view becomes a coplecated join, and it may take 
a

long time to complete








- Original Message - 
From: "Andrej Ricnik-Bay" <[EMAIL PROTECTED]>

To: "Ashish Karalkar" <[EMAIL PROTECTED]>; "Postgres
General" 
Sent: Wednesday, May 09, 2007 12:08 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages



On 5/9/07, Ashish Karalkar <[EMAIL PROTECTED]> wrote:

Hello All,

Hi,


Can anybody please point me to Advantages and Disadvantages
of using view

The most obvious advantage (for me, anyway) is to restrict which
columns a user can see in a table.

I'm sure there are others.





With Regards
Ashish...

Cheers,
Andrej


--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes
concise.

http://www.american.edu/econ/notes/htmlmail.htm

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



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



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

[GENERAL] backup and restore

[anhtin]

hi all
I am developing web site use database is PostGres and now i must build
function backup and Restore database on Web
My web develop on framework 2.0(asp.net 2.0)
I know have two file on forder bin use backup and restore is: pg_restore.exe
, pg_dump.exe
I have plan is write function or procedures on Database can backup and
restore database.
How can i write this function ???
Some body show for me?
-- 
View this message in context: 
http://www.nabble.com/backup-and-restore-tf3714247.html#a10389799
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

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

Re: [GENERAL] Views- Advantages and Disadvantages

[Ashish Karalkar]

Thanks Andrej
for your replay

I have found out some of them n are as follows, but  I want more reasons 
for not using views . I only got one


Advantages:

 1)  Permission to user can be given to access the database only 
through view containing specific data the user is authorized to see


 2)   View  convert multiple table queries into single table 
queries


3)   Views give personalized view

4)   Views provide consistent unchanged image even if the underlying 
table columns changed.




Disadvantages:



1)  Performance : If a view is defined by complex multitable query,then 
simple query against that view becomes a coplecated join, and it may take a 
long time to complete









- Original Message - 
From: "Andrej Ricnik-Bay" <[EMAIL PROTECTED]>
To: "Ashish Karalkar" <[EMAIL PROTECTED]>; "Postgres 
General" 

Sent: Wednesday, May 09, 2007 12:08 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages



On 5/9/07, Ashish Karalkar <[EMAIL PROTECTED]> wrote:

Hello All,

Hi,


Can anybody please point me to Advantages and Disadvantages
of using view

The most obvious advantage (for me, anyway) is to restrict which
columns a user can see in a table.

I'm sure there are others.





With Regards
Ashish...

Cheers,
Andrej


--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes 
concise.


http://www.american.edu/econ/notes/htmlmail.htm

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



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