Re: [GENERAL] Built in function question

[Joe Conway]

Tony Caduto wrote:

[question about finding data directory]

I found it in the pg_settings view, but if there is another way I would 
like to know about it.




As Tom aluded, you can also do this:

regression=# select current_setting('data_directory');
  current_setting
---
 /usr/local/pgsql-8.0/data
(1 row)

Joe

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

Re: [GENERAL] Disappearing Records

[Venki]

 
 Hi Martijn van Oosterhout   ,
 
This is the output that I get by running the query 
SELECT datname, age(datfrozenxid) FROM pg_database;
 

datname | age
+--
MyProd | 10014107
MyProdtest | 10014107
template1 | 10014107
template0 | 10014107
MyDb | 10014107
(5 rows)
Regards
Venki
---Original Message---
 

From: Martijn van Oosterhout
Date: 11/02/05 17:47:26
To: Venki
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Disappearing Records
 
On Wed, Nov 02, 2005 at 05:00:35PM +0530, Venki wrote:
> >The really nasty thing about it is that because the records are now
> >considered really old, as soon as you do run VACUUM it'll start
> >removing the rows you want to save...
 
> So does this mean that when we do a vacuum for the first time there will
> still be data loss or Am I wrong in this?
 
VACUUM cannot recover data from transaction wraparound. But we havn't
even determined if this has happened as you have not yet posted the
output of this query:
 
SELECT datname, age(datfrozenxid) FROM pg_database;
 
Have a ncie day,
--
Martijn van Oosterhout      http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
 
 

Re: [GENERAL] Built in function question

[Tom Lane]

Tony Caduto <[EMAIL PROTECTED]> writes:
> Does anyone know if there is a function that can return the path to the 
> data directory?

In 8.0 and later,
SHOW data_directory;
or the equivalent function call.

regards, tom lane

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

Re: [GENERAL] Built in function question

[Tony Caduto]

I found it in the pg_settings view, but if there is another way I would 
like to know about it.


Thanks,

Tony

Tony Caduto wrote:

Does anyone know if there is a function that can return the path to 
the data directory?


I was looking through the docs, but could not find anything.

Thanks,



--
Tony Caduto
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql 8.x 



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

[GENERAL] Built in function question

[Tony Caduto]

Does anyone know if there is a function that can return the path to the 
data directory?


I was looking through the docs, but could not find anything.

Thanks,

--
Tony Caduto
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql 8.x 



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

Re: [GENERAL] Changing ids conflicting with serial values?

[Douglas McNaught]

Steven Brown <[EMAIL PROTECTED]> writes:

> I'm granting access to insert/update/delete rows of a table to people,
> but I don't want all future inserts to fail if they decided to change
> an id (which they obviously shouldn't, but they /can/).  It makes for
> a fragile system.

If it shouldn't happen, you should enforce that with a trigger.  Easy
enough to do.

But giving access to an SQL prompt to people who don't know why you
shouldn't change a PK ID scares me.  :)

-Doug

---(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] Changing ids conflicting with serial values?

[Joshua D. Drake]

Steven Brown wrote:

When I change an id (primary key serial) in a table, the next value 
returned by the sequence for the id can conflict with that id (e.g., 
change the id to be id + 1).  MySQL seems to handle this transparently 
by skipping conflicting values, but with PostgreSQL I get primary key 
conflicts.  It seems rather bad if a user can modify an id in a row 
and cause failures for all future inserts - it's just too fragile.  
What's the proper way to handle this in PostgreSQL?



Why are your users modifying the key in the first place? Typically in an 
environment where you have an autoincrementing key, that key is static. 
Meaning it does not change for a particular
row. Thus it never conflicts. Could you perhaps explain a little further 
what it is you are trying to do?


Sincerely,

Joshua D. Drake





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




--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


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

Re: [GENERAL] Changing ids conflicting with serial values?

[Steven Brown]

Tom Lane wrote:

Steven Brown <[EMAIL PROTECTED]> writes:
When I change an id (primary key serial) in a table, the next value 
returned by the sequence for the id can conflict with that id (e.g., 
change the id to be id + 1).

[...]

Plan A: don't do that.  Why in the world is it a good idea to modify an
artificial primary key?  It's not like there's some external meaning to
the values.


I'm granting access to insert/update/delete rows of a table to people, 
but I don't want all future inserts to fail if they decided to change an 
id (which they obviously shouldn't, but they /can/).  It makes for a 
fragile system.


Should I just be using some sort of trigger to block them from modifying 
the id, or is there another way to handle it?  I.e., how do people 
normally handle that?  It's a migration thing - MySQL prevented this 
situation due to the way it handles auto_increment (it will never assign 
you an id that already exists).



---(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] Problem with array in plpgsql function .. please help :-)

[David Gagnon]

Hi all,

 I cannot find what is the problem with my function below.  The 
following line in the function  :AND PD.PDPONUM = ANY (receivingIds)


don't work.  If I change this line byAND PD.PDPONUM = 1734 (Hardcode 
a given value) I get a result row. 


When I call the same function

select * from usp_Commande_Dues_Retourner('{\'1734\'}', 'M', 
'2005-02-02', '2005-11-02', 'EN' );


with the real line I get no result? 

Any idea? What is the difference between AND PD.PDPONUM = 1734  and  
AND PD.PDPONUM = ANY (receivingIds)




Thanks for your help .. I'm messing around this problem for several 
hours now .. and haven't found the answer yet.


Best Regard
/David



CREATE OR REPLACE FUNCTION usp_Commande_Dues_Retourner(VARCHAR[], 
VARCHAR, DATE, DATE, VARCHAR) RETURNS refcursor  AS $$

DECLARE

receivingIds ALIAS FOR $1;
companyId ALIAS FOR $2;
fromReceptionDate ALIAS FOR $3;
toReceptionDate ALIAS FOR $4;
warehouseId ALIAS FOR $5;
BEGIN

   OPEN ref FOR SELECT BD.BDNUM, BDYPNUM, BORRNUMC, RRDESC, BONUM, 
BD.BDDTDUEA, BD.BDICNUM, (BD.BDPRIXNET * BD.BDQAEXPV) AS Total, 
BD.BDQAEXPV, IQQSTOCK - IQQRESV AS IQQSTOCK, BD.BDDTDUEV, T_IC2.ICQTE

   FROM BD INNER JOIN (

 SELECT BDICNUM, SUM(BDQAEXPV) AS ICQTE
 FROM BD
 INNER JOIN BO ON BD.BDBONUM = BO.BONUM AND BD.BDYPNUM 
= BO.BOYPNUM
 INNER JOIN PD ON BD.BDNUM = PD.PDBDNUM AND BD.BDYPNUM 
= PD.PDYPNUM

 WHERE BDSTATV = 3
   AND BDAENUM = warehouseId
   AND BOTYPE = 0
   AND BOSTATUT IN (0, 1)
   AND fromReceptionDate::DATE  <=  BODTCOM::DATE
   AND toReceptionDate::DATE  >=  BODTCOM::DATE
  AND PD.PDPONUM = ANY (receivingIds)
   AND BD.BDYPNUM = companyId
 GROUP BY BDICNUM

   ) AS T_IC2 ON BD.BDICNUM = T_IC2.BDICNUM
   INNER JOIN BO ON BD.BDBONUM = BO.BONUM  AND 
BD.BDYPNUM = BO.BOYPNUM

   INNER JOIN RR ON BO.BORRNUMC = RR.RRNUM
   LEFT OUTER JOIN IQ ON BD.BDICNUM = IQ.IQICNUM AND 
BD.BDAENUM = IQ.IQAENUM

   WHERE BD.BDSTATV = 3
   AND BDAENUM = warehouseId
   AND BOTYPE = 0
   AND BOSTATUT IN (0, 1)
   AND fromReceptionDate::DATE <=  BODTCOM::DATE
   AND toReceptionDate::DATE >=  BODTCOM::DATE
   AND BD.BDYPNUM = companyId
   ORDER BY BONUM, BDICNUM, BDQAEXPV ;
   RETURN ref;

END;
$$ LANGUAGE 'plpgsql';

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

  http://archives.postgresql.org

Re: [GENERAL] SQL injection

[Alex Turner]

Curiously none are security reasons, they are more portability reasons
(and pretty thin ones at that)... but then this is PHP we are talking
about - let me just say register_globals and end it there.

I would have to say that for security purposes - I would want magic
quotes _on_ rather than off for the whole reasons of SQL Injection
that we already talked about.  Generally most scripts I write spend
more time sending data to the DB then re-reading straight from the DB
rather than re-using data pushed through POST/GET, and the functions
that are exceptions auto unescape the data again for me...

Alex

On 11/2/05, Matthew Terenzio <[EMAIL PROTECTED]> wrote:
>
> On Nov 2, 2005, at 6:08 PM, Michael Glaesemann wrote:
>
> > As an aside, it's interesting to see that the PHP documentation states:
> > ---
> > Magic Quotes is a process that automagically escapes incoming data to
> > the PHP script. It's preferred to code with magic quotes off and to
> > instead escape the data at runtime, as needed.
> Haven't been totally immersed in this thread but here are reasons given
> for not using Magic Quotes:
>
> http://us2.php.net/manual/en/security.magicquotes.whynot.php
>
> And here is pg_escape_string() :
>
> http://us3.php.net/manual/en/function.pg-escape-string.php
>
>

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

Re: [GENERAL] Changing ids conflicting with serial values?

[Tom Lane]

Steven Brown <[EMAIL PROTECTED]> writes:
> When I change an id (primary key serial) in a table, the next value 
> returned by the sequence for the id can conflict with that id (e.g., 
> change the id to be id + 1).  MySQL seems to handle this transparently 
> by skipping conflicting values, but with PostgreSQL I get primary key 
> conflicts.  It seems rather bad if a user can modify an id in a row and 
> cause failures for all future inserts - it's just too fragile.  What's 
> the proper way to handle this in PostgreSQL?

Plan A: don't do that.  Why in the world is it a good idea to modify an
artificial primary key?  It's not like there's some external meaning to
the values.

Plan B: after you do it, adjust the sequence generator with setval().
You can use max() to figure out where to set the generator.

regards, tom lane

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

[GENERAL] Changing ids conflicting with serial values?

[Steven Brown]

When I change an id (primary key serial) in a table, the next value 
returned by the sequence for the id can conflict with that id (e.g., 
change the id to be id + 1).  MySQL seems to handle this transparently 
by skipping conflicting values, but with PostgreSQL I get primary key 
conflicts.  It seems rather bad if a user can modify an id in a row and 
cause failures for all future inserts - it's just too fragile.  What's 
the proper way to handle this in PostgreSQL?



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

Re: [GENERAL] Data Dictionary generator?

[David Fetter]

On Wed, Nov 02, 2005 at 03:39:24PM -0800, Patrick Hatcher wrote:
> 
> 
> I need to generate a  data dictionary for all my tables (name, column,
> type, etc)  in my database.  Is there an easy to do this without having to
> do a \d tablename for each table?

You could use pg_dump -s to get the schema, or in psql \d by itself
gets you everything :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

Re: [GENERAL] FOSS Reporting tools (was Oracle 10g Express - any danger for Postgres?)

[Steve Atkins]

On Wed, Nov 02, 2005 at 05:26:56PM -0700, Trent Shipley wrote:
> I never really used SQL*Plus as a command line tool.  I tended to use it as a 
> weak SQL scripting language.  
> 
> Granted report generators can't be part of core PostgreSQL, they are still a 
> critical part of any database workshop.
> 
> === 
> 
> A) Are there any FOSS SQL scripting tools that output data ready for 
> reporting 
> (like SQR, but better)?
> 
> B) Are there any FOSS tools that will take data and build pretty output.
> 
> C) Are there any FOSS tools of type B that will take streaming input from 
> some 
> tool of type A.
> 
> D) Are there any FOSS tools that combine both A and B into one low learning 
> curve package like Crystal Reports.

Not split up in that way, but Jasper Reports, OpenRPT and BIRT are
three reporting tools that have GUI designers available for them - I
believe all three are band-based engines. The learning curve isn't
entirely trivial, but it's certainly quite managable by end users.

(There are many, many others that don't have a GUI designer
 available. There's at least one on pgfoundry that explicitly supports
 postgresql. They usually seem to take XML as an input template and
 render to HTML or PDF.)

Cheers,
  Steve


---(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] FOSS Reporting tools (was Oracle 10g Express - any

[Joshua D. Drake]

> === 
> 
> A) Are there any FOSS SQL scripting tools that output data ready for 
> reporting 
> (like SQR, but better)?

Open Office Base, OpenMFG, Jasper ...

> 
> B) Are there any FOSS tools that will take data and build pretty output.

See above.

> 
> C) Are there any FOSS tools of type B that will take streaming input from 
> some 
> tool of type A.

Unknown.

> 
> D) Are there any FOSS tools that combine both A and B into one low learning 
> curve package like Crystal Reports.

Open Office Base, Jasper...

Sincerely,

Joshua D. Drake



> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


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

[GENERAL] CDBaby.com: PostgreSQL + Ruby on Rails

[Aly Dharshi]

http://www.oreillynet.com/pub/wlg/8274

Check it out folks.

--
Aly Dharshi
[EMAIL PROTECTED]

 "A good speech is like a good dress
  that's short enough to be interesting
  and long enough to cover the subject"


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

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

[GENERAL] FOSS Reporting tools (was Oracle 10g Express - any danger for Postgres?)

[Trent Shipley]

On Wednesday 2005-11-02 13:11, Jim C. Nasby wrote:
> On Wed, Nov 02, 2005 at 01:25:31PM -0600, James Thompson wrote:
> > > Yes, sqlplus looks especially bad once you're used to banging around
> > > in psql.  Although, I recently discovered rlwrap (a generic readline
> > > wrapper) which makes sqlplus almost tolerable.  It's the best thing to
> > > happen to sqlplus since... well, since "quit" I suppose.
> >
> > I just wish pgsql had something similar to sqlplus's built in formatting
> > tools for output.  Being able to set titles, row lengths, and breaks made
> > sqlplus a very nice reporting tool.  A rather large majority of "reports"
> > at my old job consisted of sqlplus commands to set the format output, and
> > a sql statement redirected to our line printer.
> >
> > I haven't used Oracle since the mid 90s so I don't have a working example
> > but a description of some of the commands can be found here
> >
> > http://www.siue.edu/~dbock/cmis564/otext3.htm
>
> Given the choice, I'd *MUCH* rather have a good, easy-to-use CLI than a
> reporting tool. I'm not a GUI person, so I always hate working with
> Oracle and MSSQL in that regard. Of course db2's CLI is just horrid, but
> luckily it's easy to just substitute your shell for it's editing
> features, ie:
>
> db2 'select * from table'
> db2 'update ...'
>
> Believe it or not it very quickly becomes second nature to wrap
> everything in db2 '', so it's not nearly as bad as you'd think.
>
> In any case, how much user demand is there for a reporting tool for
> PostgreSQL? Either a seperate tool or better functionality in psql. My
> guess is that this isn't something that interests most of the
> developers, so the only way it's going to happen is if a lot of users
> speak up and ask for it. Of course speaking up with patches is far
> better.

I never really used SQL*Plus as a command line tool.  I tended to use it as a 
weak SQL scripting language.  

Granted report generators can't be part of core PostgreSQL, they are still a 
critical part of any database workshop.

=== 

A) Are there any FOSS SQL scripting tools that output data ready for reporting 
(like SQR, but better)?

B) Are there any FOSS tools that will take data and build pretty output.

C) Are there any FOSS tools of type B that will take streaming input from some 
tool of type A.

D) Are there any FOSS tools that combine both A and B into one low learning 
curve package like Crystal Reports.

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

   http://archives.postgresql.org

Re: [GENERAL] Lock Modes (Documentation)

[Tom Lane]

Bruce Momjian  writes:
> Jim C. Nasby wrote:
>> Would it be feasable to have the lock manager spew out info about lock
>> aquisition and release? Not only would it make getting this information
>> easy, but I suspect it could be a useful debugging tool.

> Something like log_locks?  That would be valuable.

A moment's thought would suggest that the output would be too voluminous
to be of any use whatever to ordinary users.  The capability already
exists at the developer level (see LOCK_DEBUG).

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] SQL injection

[Matthew Terenzio]

On Nov 2, 2005, at 6:08 PM, Michael Glaesemann wrote:


As an aside, it's interesting to see that the PHP documentation states:
---
Magic Quotes is a process that automagically escapes incoming data to 
the PHP script. It's preferred to code with magic quotes off and to 
instead escape the data at runtime, as needed.
Haven't been totally immersed in this thread but here are reasons given 
for not using Magic Quotes:


http://us2.php.net/manual/en/security.magicquotes.whynot.php

And here is pg_escape_string() :

http://us3.php.net/manual/en/function.pg-escape-string.php


---(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] Data Dictionary generator?

[Patrick Hatcher]

Thank you.
"select * From information_schema.columns where table_schema = 'public' and
table_name = 'mdc_products'
order by ordinal_position;"

Thanks again.

Patrick Hatcher



   
 "Dann Corbit" 
 <[EMAIL PROTECTED] 
 m> To 
   "Patrick Hatcher"   
 11/02/2005 03:45  <[EMAIL PROTECTED]>,   
 PM  
cc 
   
   Subject 
   RE: [GENERAL] Data Dictionary   
   generator?  
   
   
   
   
   
   




PostgreSQL has Information Schema

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Patrick Hatcher
> Sent: Wednesday, November 02, 2005 3:39 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Data Dictionary generator?
>
>
>
> I need to generate a  data dictionary for all my tables (name, column,
> type, etc)  in my database.  Is there an easy to do this without
having to
> do a \d tablename for each table?
> tia
> Patrick
>
>
> ---(end of
broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq



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

Re: [GENERAL] Replicating databases

[Marc Munro]

Carlos,
What you are asking for is a multi-master replication scheme.  Slony-I
is a single master system, meaning that for each slony cluster only one
node can make changes to the data.

Without breaking slony's normal rules, I believe that there might be a
way to do it, though it will not be pretty.

Basically you would create a slony cluster for each store, which
replicates store data back to the central system.  You will also have a
master cluster that replicates central data to all stores.

For each store you will create a slony cluster CS (ie for store 1, you
create cluster C1, for store 2 cluster C2, etc).

For the central (master) database you will create a cluster CM that
replicates to all stores.

For each application table, T, you will do the following:
- create a table T_S at each source store S, and on the central database
- add T_S to the replication set for CS
- on the central db create a master table T_M
- on the central db, add triggers on T_S that copy all changes into the
master table T_M (T_M will then contain the full set of data from all
stores)
- add T_M to the replication set for cluster CM
- at each store create a view T that does select * from T_S union select
* from T_M
- create instead of triggers on T that cause updates to be performed
only on the underlying local table T_S
- at the central node create a view T that does select * from T_M, (you
don't need instead of triggers for this as the data can only be updated
at the stores)

So, for N stores you will have created N+1 slony clusters, N+1 distinct
tables for each distributed table.

This is horrible and a lot of maintenance.  It might work though if the
number of stores is quite small.

You should probably ask the question again on slony1-general.  The
experts there may suggest a better solution.  I have seen talk of
disabling the standard slony triggers to allow this sort of thing but
whether that is more or less nasty is questionable.

Good luck

__
Marc

On Wed, 2005-11-02 at 12:18 -0400, [EMAIL PROTECTED]
wrote:
> Date: Wed, 2 Nov 2005 12:06:36 + (GMT)
> From: Carlos Benkendorf <[EMAIL PROTECTED]>
> To: pgsql-general@postgresql.org
> Subject: Replicating databases
> Message-ID: <[EMAIL PROTECTED]>
> 
> Hello,
>  
> Currently our company has a lot of small stores distributed around the
> country and in the actual database configuration we have a central
> database and all the small stores accessing it remotely.
>  
> All primary key tables were designed with a column identifying the
> store that it belongs. In other words, the store that can update the
> line, other stores can read it but the system was designed in such a
> way that other stores can not update information that do not belong to
> them.
>  
> The performance is not good because the line speed that connects the
> store to the central database sometimes is overloaded. Were thinking
> to replicate the central database to each store. The store would be
> able to read all the information from the local database but should
> only update lines that belong to that store. 
>  
> When a store needs read information about other stores, it is not
> necessary to be updated, it can be a yesterday snapshot.
>  
> During the night all the local store databases will be consolidated in
> only one database and replicated again to the stores. In the morning,
> when the store opens, the local database has an updated and
> consolidated data.
> I would appreciate suggestions about how the best way to implement
> such soluction.
>  
> Slony-1? SQL scripts?
>  
> Thanks in advance!
> 
> Benkendorf


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

Re: [GENERAL] Data Dictionary generator?

[Dann Corbit]

PostgreSQL has Information Schema

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Patrick Hatcher
> Sent: Wednesday, November 02, 2005 3:39 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Data Dictionary generator?
> 
> 
> 
> I need to generate a  data dictionary for all my tables (name, column,
> type, etc)  in my database.  Is there an easy to do this without
having to
> do a \d tablename for each table?
> tia
> Patrick
> 
> 
> ---(end of
broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq

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

[GENERAL] Data Dictionary generator?

[Patrick Hatcher]

I need to generate a  data dictionary for all my tables (name, column,
type, etc)  in my database.  Is there an easy to do this without having to
do a \d tablename for each table?
tia
Patrick


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

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

Re: [GENERAL] Clustered indexes - When to use them?

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> That's because unfortunately PostgreSQL only keeps statistics on
> individual columns. There's no stats kept on multi-column indexes; the
> best the planner can do is use the stats for the first column.

That's not what we do at all: we do look at the stats for each column
and combine them.

The deficiency is that we don't have any stats about cross-column
correlations, and therefore must assume that the columns are independent
(in the statistical sense).  This is a poor assumption in the real world.
But it's a lot subtler than "we only consider the first column".

regards, tom lane

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

Re: [GENERAL] Lock Modes (Documentation)

[Bruce Momjian]

Jim C. Nasby wrote:
> On Wed, Nov 02, 2005 at 05:59:15PM -0500, Bruce Momjian wrote:
> > Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
> > have ROW SHARE until 8.1.  I actually can't find out how we are doing
> > that in the code, however.  Analyzing the code is probably the only way
> > to get this detailed lock information.
> 
> Would it be feasable to have the lock manager spew out info about lock
> aquisition and release? Not only would it make getting this information
> easy, but I suspect it could be a useful debugging tool.

Something like log_locks?  That would be valuable.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] Replicating databases

[Jim C. Nasby]

On Wed, Nov 02, 2005 at 05:45:40PM -0500, Andrew Sullivan wrote:
> On Wed, Nov 02, 2005 at 12:06:36PM +, Carlos Benkendorf wrote:
> > I would appreciate suggestions about how the best way to implement
> > such soluction.
> >  
> > Slony-1? SQL scripts?
> 
> Maybe a combination.  My natural inclination would be to try to do
> this with some tricky views+rules so that each store could write into
> its own table (then everybody could replicate, and in fact you could
> have the other store data updated, but maybe not as fast as real
> time).  The problem is that in the central database, this is going to
> turn out to be a big, nasty UNION if there are more than a handful of
> stores. 
> 
> But, you could do this with some batch processing in the night at
> each store, such that you pulled local data into a special local
> table (into which you'd write, depending on your local store id) and
> the non-local table.  Again, you could use a view with rules to allow
> writing into these local tables.  Then during the batch processing at
> night, you could merge all these changes together, and prepare
> special sets to push out to the stores so that they could see
> everyone else's day old data.
> 
> It seems kludgey this way, though.  What you really need is
> multimaster with conflict resolution, because you can depend on your

Isn't Slony2 supposed to do just that?

> application to cause no conflicts.  Slony is designed to prevent you
> from writing into the replicated tables.  Some of the other
> master-slave ones don't have that restriction, but they're sort of
> dangerous for the same reason.
> 
> A
> 
> -- 
> Andrew Sullivan  | [EMAIL PROTECTED]
> The whole tendency of modern prose is away from concreteness.
>   --George Orwell
> 
> ---(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
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] Lock Modes (Documentation)

[Jim C. Nasby]

On Wed, Nov 02, 2005 at 05:59:15PM -0500, Bruce Momjian wrote:
> Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
> have ROW SHARE until 8.1.  I actually can't find out how we are doing
> that in the code, however.  Analyzing the code is probably the only way
> to get this detailed lock information.

Would it be feasable to have the lock manager spew out info about lock
aquisition and release? Not only would it make getting this information
easy, but I suspect it could be a useful debugging tool.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(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] SQL injection

[Michael Glaesemann]

On Nov 3, 2005, at 4:26 , Alex Turner wrote:


My point is that with magic_quotes on in PHP, php already escapes
quotes for you in all inbound variables.  This makes the process
automatic, and therefore fool proof, which is kinda the whole point.
You want a mechanism that there isn't an easy way around, like
forgetting to db_quote once in a while.  I'm just trying to find out
if there is an example where magic quotes by itself doesn't work, and
there is a viable injection attack possible, and if so, what it is, so
I can figure out how to prevent it ;).



I'm wondering if using magic_quotes will have issues down the pipe  
when backslash escaping is no longer the default in PostgreSQL to  
follow SQL spec. Am I correct in thinking that either the SQL  
statements would have to be rewritten to use E'' strings, the server  
setting would have to allow the use of backslashes, or magic_quotes  
would have to be turned off and variables otherwise escaped to  
prevent SQL injection?


As an aside, it's interesting to see that the PHP documentation states:
---
Magic Quotes is a process that automagically escapes incoming data to  
the PHP script. It's preferred to code with magic quotes off and to  
instead escape the data at runtime, as needed.

---
http://jp.php.net/magic_quotes

Michael Glaesemann
grzm myrealbox com




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

Re: [GENERAL] Lock Modes (Documentation)

[Bruce Momjian]

Thomas F. O'Connell wrote:
> I thought about posting to pgsql-docs, but since this might require  
> comment from developers, I thought -general might be a better  
> starting point.
> 
> Anyway, I've occasionally run into monitoring situations where it  
> would be immediately helpful to know the built-in SQL statements that  
> generate given table-lock modes.
> 
> For instance, doesn't UPDATE implicitly mean that an ACCESS SHARE  
> lock will be taken if there are foreign keys involved (at least in  
> versions prior to 8.1)? Are there any other scenarios where a given  
> SQL command might take a lock of one of these forms as a result of  
> what it does under the hood? Maybe UPDATE is the only one since it's  
> implicitly a SELECT, DELETE, and INSERT all rolled into one.
> 
> I'd love to see 12.3  explicit-locking.html> document this more thoroughly, but I don't  
> know enough about the underlying locking requirements of each step of  
> each SQL command to know when locks might implicitly be acquired.  
> Even if UPDATE is the only special case, it seems like it'd be worth  
> mentioning.

Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
have ROW SHARE until 8.1.  I actually can't find out how we are doing
that in the code, however.  Analyzing the code is probably the only way
to get this detailed lock information.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(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] Replicating databases

[Andrew Sullivan]

On Wed, Nov 02, 2005 at 12:06:36PM +, Carlos Benkendorf wrote:
> I would appreciate suggestions about how the best way to implement
> such soluction.
>  
> Slony-1? SQL scripts?

Maybe a combination.  My natural inclination would be to try to do
this with some tricky views+rules so that each store could write into
its own table (then everybody could replicate, and in fact you could
have the other store data updated, but maybe not as fast as real
time).  The problem is that in the central database, this is going to
turn out to be a big, nasty UNION if there are more than a handful of
stores. 

But, you could do this with some batch processing in the night at
each store, such that you pulled local data into a special local
table (into which you'd write, depending on your local store id) and
the non-local table.  Again, you could use a view with rules to allow
writing into these local tables.  Then during the batch processing at
night, you could merge all these changes together, and prepare
special sets to push out to the stores so that they could see
everyone else's day old data.

It seems kludgey this way, though.  What you really need is
multimaster with conflict resolution, because you can depend on your
application to cause no conflicts.  Slony is designed to prevent you
from writing into the replicated tables.  Some of the other
master-slave ones don't have that restriction, but they're sort of
dangerous for the same reason.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The whole tendency of modern prose is away from concreteness.
--George Orwell

---(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] Clustered indexes - When to use them?

[Jim C. Nasby]

On Wed, Nov 02, 2005 at 10:55:36PM +0100, MaXX wrote:
> Jim C. Nasby wrote:
> > On Wed, Nov 02, 2005 at 08:50:45PM +0100, MaXX wrote:
> [...]
> >> In simple words:
> >> Clustered indexes are like the alphabetical index in a book, where term
> >> are randomly distibuted in the book and regular indexes are more like the
> >> table of content...
> >> Right?
> > You have that backwards. The TOC matches the ordering of the book
> > (table). Think of it as the book is clustered on the TOC. Stuff from the
> > index appears all over; it's not clustered.
> > Keep in mind that for PostgreSQL it's simply a matter of correlation.
> > You can actually see correlation in one of system views. The higher the
> > correlation between an index and the table, the more efficient index
> > scans will be.
> pgAdmin shows a correlation value in the statistics panel when I click on a
> column... Not sure if it is the right one as all column have it, look more
> correlation between values in the column...
> Still have a *lot* of things to learn...

That's because unfortunately PostgreSQL only keeps statistics on
individual columns. There's no stats kept on multi-column indexes; the
best the planner can do is use the stats for the first column.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

[GENERAL] Lock Modes (Documentation)

[Thomas F. O'Connell]

I thought about posting to pgsql-docs, but since this might require  
comment from developers, I thought -general might be a better  
starting point.


Anyway, I've occasionally run into monitoring situations where it  
would be immediately helpful to know the built-in SQL statements that  
generate given table-lock modes.


For instance, doesn't UPDATE implicitly mean that an ACCESS SHARE  
lock will be taken if there are foreign keys involved (at least in  
versions prior to 8.1)? Are there any other scenarios where a given  
SQL command might take a lock of one of these forms as a result of  
what it does under the hood? Maybe UPDATE is the only one since it's  
implicitly a SELECT, DELETE, and INSERT all rolled into one.


I'd love to see 12.3  document this more thoroughly, but I don't  
know enough about the underlying locking requirements of each step of  
each SQL command to know when locks might implicitly be acquired.  
Even if UPDATE is the only special case, it seems like it'd be worth  
mentioning.


--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)


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

Re: [GENERAL] Clustered indexes - When to use them?

[MaXX]

Scott Marlowe wrote:
> On Wed, 2005-11-02 at 13:50, MaXX wrote:
[...]
>> In simple words:
>> Clustered indexes are like the alphabetical index in a book, where term
>> are randomly distibuted in the book and regular indexes are more like the
>> table of content...
>> Right?
> Not really.  It's more like if someone reordered the book so that it was
> in the same order as whatever index you have in it.  So, if the index
> was on the length of the words, the words in the book would be reordered
> to be smallest to largest (or reverse that).  If the index were on just
> the words themselves, then the book would have the words reordered from
> A to Z etc...
Things become more clear now... 

> Note that clustered indexes do not dynamically update the table order in
> postgresql.  If you're gonna insert to them, you need to recluster them
> every so often.
Good to know it... 

Thanks,
-- 
MaXX


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

[GENERAL] Function to insert entire row%ROWTYPE into other table

[Sven Willenberger]

Postgresql 8.0.4 using plpgsql

The basic function is set up as:
CREATE FUNCTION add_data(t_row mytable) RETURNS VOID AS $func$
DECLARE
newtable text;
thesql text;
BEGIN
INSERT INTO newtable thename from mytable where lookup.id =
t_row.id;
thesql := 'INSERT INTO ' || newtable || VALUES (' || t_row.* ')';
EXECUTE thesql;
RETURN;
END;
$func$ LANGUAGE plpgsql VOLATILE;

SELECT add_data(t.*) FROM mytable t where 
ERROR:  column "*" not found in data type mytable

Now I have tried to drop the * but then there is no concatenation
function to join text to a table%ROWTYPE. So my question is how can I
make this dynamic insert statement without listing out every
t_row.colname?

SVen


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

CLI Reporting Tool Was: Re: [GENERAL] Oracle 10g Express - any danger for Postgres?

[James Thompson]

> >
> > I haven't used Oracle since the mid 90s so I don't have a working example
> > but a description of some of the commands can be found here
> >
> > http://www.siue.edu/~dbock/cmis564/otext3.htm
>
> Given the choice, I'd *MUCH* rather have a good, easy-to-use CLI than a
> reporting tool.  I'm not a GUI person, so I always hate working with 
> Oracle and MSSQL in that regard.

Exactly.  We had paid for support for SQL*Reports at that job but never used 
it.  We either used those sqlplus commands listed in the above URL or a 
Fortran(don't ask) program.  

> In any case, how much user demand is there for a reporting tool for
> PostgreSQL? Either a seperate tool or better functionality in psql. 

I'd like to see it in psql myself.

> Of course speaking up with patches is far better.

I simply don't have the time to throw at it :(  However now that I think about 
it I believe one of the coders in our project (www.gnuenterprise.org) started 
writing a tool a few years back based upon our db interfaces to provide a  
cross db sqlplus like CLI.  I'll ask him how far he got into it.

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

Re: [GENERAL] Clustered indexes - When to use them?

[MaXX]

Jim C. Nasby wrote:
> On Wed, Nov 02, 2005 at 08:50:45PM +0100, MaXX wrote:
[...]
>> In simple words:
>> Clustered indexes are like the alphabetical index in a book, where term
>> are randomly distibuted in the book and regular indexes are more like the
>> table of content...
>> Right?
> You have that backwards. The TOC matches the ordering of the book
> (table). Think of it as the book is clustered on the TOC. Stuff from the
> index appears all over; it's not clustered.
> Keep in mind that for PostgreSQL it's simply a matter of correlation.
> You can actually see correlation in one of system views. The higher the
> correlation between an index and the table, the more efficient index
> scans will be.
pgAdmin shows a correlation value in the statistics panel when I click on a
column... Not sure if it is the right one as all column have it, look more
correlation between values in the column...
Still have a *lot* of things to learn...
 
> For some other databases, when you cluster on an index the table
> actually *becomes an index*. This means that doing an index scan is
> actually the same as a table/sequential scan, except that you can easily
> find an exact place to start. Because of this, a 'clustered table' (or
> an Index Organized Table as Oracle calls it) can be extremely fast for
> certain operations.
Ok now I know why I can only have a single clustered index per table...
 
> In any case, remember the first rule of all performance tuning: don't.
> And the second rule: if you're going to, you better have metrics to
> measure your tuning with to make sure it's worth it.
Yeah! EXPLAIN ANALYSE, time, systat and friends...

Thanks again,
-- 
MaXX

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

[GENERAL] database owner does not own public schema

[Kevin Murphy]

Why, when I create a new database owned by a specified user (createdb 
--owner=somebody), would the public schema in that database not be owned 
by the user and moreover not be writable by the user?  I'm using a fresh 
install of 8.1rc1, but the same thing seems to happen with an unfresh 8.0.


Thanks,
Kevin Murphy


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

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

Re: [GENERAL] Clustered indexes - When to use them?

[Jim C. Nasby]

On Wed, Nov 02, 2005 at 08:50:45PM +0100, MaXX wrote:
> Ok thank you,
> so I can consider using clustered indexes when I need to 'reorder' random
> data to improve the speed of a particular query...
> 
> In simple words:
> Clustered indexes are like the alphabetical index in a book, where term are
> randomly distibuted in the book and regular indexes are more like the table
> of content...
> Right?

You have that backwards. The TOC matches the ordering of the book
(table). Think of it as the book is clustered on the TOC. Stuff from the
index appears all over; it's not clustered.

Keep in mind that for PostgreSQL it's simply a matter of correlation.
You can actually see correlation in one of system views. The higher the
correlation between an index and the table, the more efficient index
scans will be.

For some other databases, when you cluster on an index the table
actually *becomes an index*. This means that doing an index scan is
actually the same as a table/sequential scan, except that you can easily
find an exact place to start. Because of this, a 'clustered table' (or
an Index Organized Table as Oracle calls it) can be extremely fast for
certain operations.

In any case, remember the first rule of all performance tuning: don't.
And the second rule: if you're going to, you better have metrics to
measure your tuning with to make sure it's worth it.

Feel free to call me at work if you still have questions.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] Oracle 10g Express - any danger for Postgres?

[Jim C. Nasby]

On Wed, Nov 02, 2005 at 01:25:31PM -0600, James Thompson wrote:
> > Yes, sqlplus looks especially bad once you're used to banging around
> > in psql.  Although, I recently discovered rlwrap (a generic readline
> > wrapper) which makes sqlplus almost tolerable.  It's the best thing to
> > happen to sqlplus since... well, since "quit" I suppose.
> 
> I just wish pgsql had something similar to sqlplus's built in formatting 
> tools 
> for output.  Being able to set titles, row lengths, and breaks made sqlplus a 
> very nice reporting tool.  A rather large majority of "reports" at my old job 
> consisted of sqlplus commands to set the format output, and a sql statement 
> redirected to our line printer.
> 
> I haven't used Oracle since the mid 90s so I don't have a working example but 
> a description of some of the commands can be found here
> 
> http://www.siue.edu/~dbock/cmis564/otext3.htm

Given the choice, I'd *MUCH* rather have a good, easy-to-use CLI than a
reporting tool. I'm not a GUI person, so I always hate working with
Oracle and MSSQL in that regard. Of course db2's CLI is just horrid, but
luckily it's easy to just substitute your shell for it's editing
features, ie:

db2 'select * from table'
db2 'update ...'

Believe it or not it very quickly becomes second nature to wrap
everything in db2 '', so it's not nearly as bad as you'd think.

In any case, how much user demand is there for a reporting tool for
PostgreSQL? Either a seperate tool or better functionality in psql. My
guess is that this isn't something that interests most of the
developers, so the only way it's going to happen is if a lot of users
speak up and ask for it. Of course speaking up with patches is far
better.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

   http://archives.postgresql.org

Re: [GENERAL] Clustered indexes - When to use them?

[Scott Marlowe]

On Wed, 2005-11-02 at 13:50, MaXX wrote:
> Ok thank you,
> so I can consider using clustered indexes when I need to 'reorder' random
> data to improve the speed of a particular query...
> 
> In simple words:
> Clustered indexes are like the alphabetical index in a book, where term are
> randomly distibuted in the book and regular indexes are more like the table
> of content...
> Right?

Not really.  It's more like if someone reordered the book so that it was
in the same order as whatever index you have in it.  So, if the index
was on the length of the words, the words in the book would be reordered
to be smallest to largest (or reverse that).  If the index were on just
the words themselves, then the book would have the words reordered from
A to Z etc...

Note that clustered indexes do not dynamically update the table order in
postgresql.  If you're gonna insert to them, you need to recluster them
every so often.

---(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] looking for multi-language app example using postgresql

[Robert Treat]

On Wednesday 02 November 2005 08:57, YL wrote:
> I tried but have very little progress on this. If any one know where to
> find an multi-language example (php prefered) using postgresql, please let
> me know. Thanks
> [EMAIL PROTECTED]

phppgadmin runs in a slew of languages against a slew of encodings...I'd start 
there. 

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---(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] Clustered indexes - When to use them?

[MaXX]

Ok thank you,
so I can consider using clustered indexes when I need to 'reorder' random
data to improve the speed of a particular query...

In simple words:
Clustered indexes are like the alphabetical index in a book, where term are
randomly distibuted in the book and regular indexes are more like the table
of content...
Right?

Thanks again,
MaXX

Jim C. Nasby wrote:
> The key expense in doing an index scan is the amount of randomness
> involved in reading the base table. If a table is in the same order as
> the index then reading the base table will be very fast. If the table is
> in a completely random order compared to an index (it's correlation is
> low), then an index scan becomes very expensive because every row you
> read out of the index means seeking to a random page in the table.
> 
> So, if you do a lot of querying on the table that would work best with
> an index scan, it's probably worth it to cluster on that index.
> 
> Note that I'm talking about index *scans* here, where you're pulling a
> decent number of rows.
> 
> There's some other considerations as well, but this is probably the
> biggest one.
> 

-- 
MaXX


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

Re: [GENERAL] SQL injection

[Alex Turner]

My point is that with magic_quotes on in PHP, php already escapes
quotes for you in all inbound variables.  This makes the process
automatic, and therefore fool proof, which is kinda the whole point. 
You want a mechanism that there isn't an easy way around, like
forgetting to db_quote once in a while.  I'm just trying to find out
if there is an example where magic quotes by itself doesn't work, and
there is a viable injection attack possible, and if so, what it is, so
I can figure out how to prevent it ;).

Alex.

On 11/1/05, Matthew D. Fuller <[EMAIL PROTECTED]> wrote:
> On Tue, Nov 01, 2005 at 08:57:04AM -0500 I heard the voice of
> Tom Lane, and lo! it spake thus:
> >
> > If you rely on applying an escaping function then it's pretty easy
> > to forget it in one or two places, and it only takes one hole to be
> > vulnerable :-(.
>
> The trick is to make it a religious ritual.  I escape things into _q
> variables:
>
> $name = $_REQUEST['name'];
> $name_q = db_quote($name);
>
> And have myself thoroughly trained to ONLY use _q variables in
> building queries.  Of course, once in a while, I forget to _create_
> the _q version before using it, but then I get a nice loud error
> message castigating me for it.  I often (not consistently) create _q
> variables even for known-good strings and such that I hardcode into
> the program.
>
> It could well be that using prepared statements is by various metrics
> a "better" way to go about things.  But I'm far too lazy to try and
> reprogram my fingers;-)
>
>
> --
> Matthew Fuller (MF4839)   |  [EMAIL PROTECTED]
> Systems/Network Administrator |  http://www.over-yonder.net/~fullermd/
>On the Internet, nobody can hear you scream.
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
>

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

Re: [GENERAL] Oracle 10g Express - any danger for Postgres?

[James Thompson]

> Yes, sqlplus looks especially bad once you're used to banging around
> in psql.  Although, I recently discovered rlwrap (a generic readline
> wrapper) which makes sqlplus almost tolerable.  It's the best thing to
> happen to sqlplus since... well, since "quit" I suppose.

I just wish pgsql had something similar to sqlplus's built in formatting tools 
for output.  Being able to set titles, row lengths, and breaks made sqlplus a 
very nice reporting tool.  A rather large majority of "reports" at my old job 
consisted of sqlplus commands to set the format output, and a sql statement 
redirected to our line printer.

I haven't used Oracle since the mid 90s so I don't have a working example but 
a description of some of the commands can be found here

http://www.siue.edu/~dbock/cmis564/otext3.htm

Take Care,
James

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

   http://archives.postgresql.org

Re: [GENERAL] PostgreSQL on 64-bit operating systems

[Vivek Khera]

On Nov 1, 2005, at 5:12 PM, Jim C. Nasby wrote:

My understanding is that there is no 32 bit version of FBSD on  
Opterons;

as soon as buildworld sees it's on an Opteron everything goes 64 bit.



you would understand incorrectly, then. freebsd will never flip you  
up to 64 bit world like that. it would break so many things it'd  
leave your head spinning in shock if it did that.



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

[GENERAL] Linking

[Bob Pawley]

I very much wish to thank Oliver and Michael for their help in getting 
started. I was scratching around the edges of trigger functions without being 
able to put everything in its proper place.
 
I now have a way of moving forward with some glimmer of eventually knowing 
what I am doing.
 
Thanks again.
 
Bob Pawley
 

Re: [GENERAL] Clustered indexes - When to use them?

[Jim C. Nasby]

The key expense in doing an index scan is the amount of randomness
involved in reading the base table. If a table is in the same order as
the index then reading the base table will be very fast. If the table is
in a completely random order compared to an index (it's correlation is
low), then an index scan becomes very expensive because every row you
read out of the index means seeking to a random page in the table.

So, if you do a lot of querying on the table that would work best with
an index scan, it's probably worth it to cluster on that index.

Note that I'm talking about index *scans* here, where you're pulling a
decent number of rows.

There's some other considerations as well, but this is probably the
biggest one.

On Wed, Nov 02, 2005 at 02:04:31PM +0100, MaXX wrote:
> Hi,
> Is there any "rule of thumb" on when to (not) use clustered indexes?
> What appen to the table/index? (any change on the physical organisation?)
> I've seen speed improvement on some queries but I'm not sure if I must use
> them or not...
> 
> My rows are imported in batch of 100 (once the main script has collected
> them, this takes between 1 and 30min), then another script vacuums the
> table and aggregate the last imported rows,  if I add a column with the
> commit timestamp and cluster on it, will I gain some perfs or not?
> 
> Thanks,
> -- 
> MaXX
> 
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] md5 hash on table row

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Wed, Nov 02, 2005 at 06:07:45PM +0100, A. Kretschmer wrote:
>> is 'record_out()' new in 8.1?

> The signature has changed over time:

> 7.3  record_out(record)
> 7.4  record_out(record)
> 8.0  record_out(record,oid)
> 8.1  record_out(record)

BTW, the addition of OID to the signature was a bad idea (read security
hole), and the more recent 8.0.* subreleases ignore it.  So you can just
pass a zero instead of worrying about figuring out the right type OID.
Indeed, Michael's example is formally wrong:

SELECT id, textin(record_out(row(foo), tableoid)) FROM foo;

What record_out is getting here is not a foo; it's a record type that
contains one column that is a foo.  Since tableoid is the type of foo,
it does not correctly describe the record.  This example would probably
crash an 8.0.0 server, because it would believe the OID argument :-(

Here's the CVS log entry:

2005-04-30 16:04  tgl

* src/backend/utils/adt/rowtypes.c (REL8_0_STABLE): Make record_out
and record_send extract type information from the passed record
object itself, rather than relying on a second OID argument to be
correct.  This patch just changes the function behavior and not the
catalogs, so it's OK to back-patch to 8.0.  Will remove the
now-redundant second argument in pg_proc in a separate patch in
HEAD only.

and for 8.1 we did this:

2005-05-01 14:56  tgl

* doc/src/sgml/ref/create_type.sgml,
src/backend/access/common/printtup.c,
src/backend/bootstrap/bootstrap.c, src/backend/commands/copy.c,
src/backend/commands/typecmds.c, src/backend/executor/spi.c,
src/backend/nodes/print.c, src/backend/tcop/fastpath.c,
src/backend/utils/adt/arrayfuncs.c,
src/backend/utils/adt/rowtypes.c,
src/backend/utils/adt/ruleutils.c, src/backend/utils/adt/varlena.c,
src/backend/utils/cache/lsyscache.c, src/backend/utils/misc/guc.c,
src/include/utils/lsyscache.h, src/pl/plperl/plperl.c,
src/pl/plpgsql/src/pl_exec.c, src/pl/tcl/pltcl.c: Change CREATE
TYPE to require datatype output and send functions to have only one
argument.  (Per recent discussion, the option to accept multiple
arguments is pretty useless for user-defined types, and would be a
likely source of security holes if it was used.)  Simplify call
sites of output/send functions to not bother passing more than one
argument.

regards, tom lane

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

Re: [GENERAL] md5 hash on table row

[Bruce Momjian]

Michael Fuhr wrote:
> On Wed, Nov 02, 2005 at 06:07:45PM +0100, A. Kretschmer wrote:
> > am  02.11.2005, um  9:35:33 -0700 mailte Michael Fuhr folgendes:
> > > test=> SELECT id, md5(textin(record_out(foo))) FROM foo;
> > 
> > is 'record_out()' new in 8.1?
> 
> The signature has changed over time:
> 
> 7.3  record_out(record)
> 7.4  record_out(record)
> 8.0  record_out(record,oid)
> 8.1  record_out(record)
> 
> As I already discovered and as Tom pointed out, the cast from foo
> to record works only in 8.1.  In 8.0 you can use row(foo), but that
> adds even more decoration to the output.

Where should we document this?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

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

Re: [GENERAL] Postgres + CentOS

[Tony Caduto]

Hugo wrote:

Hi, is anybody using psql and CentOS, I just wanted to know your 
experience


thanks

Hugo


Works great, could not be happier.
I am also running one CentOS server in 64bit mode, I built Postgres from 
source on this box because I could not find a RPM at the time.


No problems and the performance is very good.

Tony Caduto
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql 7.4.x and up

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

Re: [GENERAL] md5 hash on table row

[Michael Fuhr]

On Wed, Nov 02, 2005 at 06:07:45PM +0100, A. Kretschmer wrote:
> am  02.11.2005, um  9:35:33 -0700 mailte Michael Fuhr folgendes:
> > test=> SELECT id, md5(textin(record_out(foo))) FROM foo;
> 
> is 'record_out()' new in 8.1?

The signature has changed over time:

7.3  record_out(record)
7.4  record_out(record)
8.0  record_out(record,oid)
8.1  record_out(record)

As I already discovered and as Tom pointed out, the cast from foo
to record works only in 8.1.  In 8.0 you can use row(foo), but that
adds even more decoration to the output.

-- 
Michael Fuhr

---(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] md5 hash on table row

[Michael Fuhr]

On Wed, Nov 02, 2005 at 12:18:15PM -0500, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > I just noticed that record_out(foo) works only in 8.1.  When I have
> > more time I'll see if it's possible in earlier versions.
> 
> Probably not :-(

This works in 8.0.4, although it gives even more decoration:

test=> SELECT id, textin(record_out(row(foo), tableoid)) FROM foo;
 id |textin 
+---
  1 | ("(1,123,""this is a test"",2005-11-02,t,""000001002"")")
(1 row)

test=> SELECT id, md5(textin(record_out(row(foo), tableoid))) FROM foo;
 id |   md5
+--
  1 | 3e66ee01b83eeb1a2444df326b75ffe0
(1 row)

-- 
Michael Fuhr

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

Re: [GENERAL] md5 hash on table row

[Michael Fuhr]

On Wed, Nov 02, 2005 at 02:49:57PM -0200, Jon Lapham wrote:
> Michael Fuhr wrote:
> >test=> SELECT id, foo FROM foo;
> > id |   foo   
> >+-
> >  1 | (1,123,"this is a test",2005-11-02,t,"\\000\\001\\002")
> 
> Perfect!  Wow, in all these years of using PostgreSQL, I did not know 
> you can "SELECT tablename FROM tablename".

It only works in 8.0 and later.

> PS: Does anyone know if this very portable to other databases?

Dunno, but it doesn't work in MySQL 5.0.15.

-- 
Michael Fuhr

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

Re: [GENERAL] md5 hash on table row

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> I just noticed that record_out(foo) works only in 8.1.  When I have
> more time I'll see if it's possible in earlier versions.

Probably not :-(

2005-05-04 20:19  tgl

* src/backend/parser/parse_coerce.c: Allow implicit cast from any
named composite type to RECORD.  At the moment this has no
particular use except to allow table rows to be passed to
record_out(), but that case seems to be useful in itself per recent
example from Elein.  Further down the road we could look at letting
PL functions be declared to accept RECORD parameters.

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] md5 hash on table row

[A. Kretschmer]

am  02.11.2005, um  9:35:33 -0700 mailte Michael Fuhr folgendes:
> test=> SELECT id, md5(textin(record_out(foo))) FROM foo;

is 'record_out()' new in 8.1?


Regards, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

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

Re: [GENERAL] md5 hash on table row

[Michael Fuhr]

On Wed, Nov 02, 2005 at 09:35:33AM -0700, Michael Fuhr wrote:
> test=> SELECT id, md5(textin(record_out(foo))) FROM foo;
>  id |   md5
> +--
>   1 | b1cbe3d5ed304f31da57b85258f20c8f

I just noticed that record_out(foo) works only in 8.1.  When I have
more time I'll see if it's possible in earlier versions.

-- 
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] md5 hash on table row

[Jon Lapham]

Michael Fuhr wrote:

test=> SELECT id, foo FROM foo;
 id |   foo   
+-

  1 | (1,123,"this is a test",2005-11-02,t,"\\000\\001\\002")


Perfect!  Wow, in all these years of using PostgreSQL, I did not know 
you can "SELECT tablename FROM tablename".


Thanks Michael.

PS: Does anyone know if this very portable to other databases?

-Jon

--
-**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*---
 Jon Lapham  <[EMAIL PROTECTED]>Rio de Janeiro, Brasil
 Personal: http://www.jandr.org/
***-*--**---***---


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

Re: [GENERAL] Disappearing Records

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Douglas McNaught <[EMAIL PROTECTED]> writes:

> Rory Browne <[EMAIL PROTECTED]> writes:
>> select u.username, g.groupname from users u, groups g where u.group_id=g.id
>> (assuming users are in exactly one group)
>> 
>> If the group_id field in the users table was corrupted, and set to a
>> value that isn't in the groups table, then that view wouldn't return
>> anything.

> That's why foreign key constraints are good.  :)

Well, Rory already confessed that he came from a MySQL background :-)


---(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] md5 hash on table row

[Michael Fuhr]

On Wed, Nov 02, 2005 at 11:38:46AM -0200, Jon Lapham wrote:
> I would love something like this:
> select id, md5(*) from mytable;

Is it acceptable to have some decoration around the data being
hashed?  If so then this example might be useful:

test=> SELECT * FROM foo;
 id | integer |  text  |date| boolean |bytea 
+-+++-+--
  1 | 123 | this is a test | 2005-11-02 | t   | \000\001\002
(1 row)

test=> SELECT id, foo FROM foo;
 id |   foo   
+-
  1 | (1,123,"this is a test",2005-11-02,t,"\\000\\001\\002")
(1 row)

test=> SELECT id, md5(textin(record_out(foo))) FROM foo;
 id |   md5
+--
  1 | b1cbe3d5ed304f31da57b85258f20c8f
(1 row)

-- 
Michael Fuhr

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

Re: [GENERAL] Postgres + CentOS

[Scott Marlowe]

On Wed, 2005-11-02 at 08:40, Hugo wrote:
> Hi, is anybody using psql and CentOS, I just wanted to know your
> experience 

We use Centos (RHEL 3 and 4 flavors) as our internal server os with
postgresql 7.4.7 / 7.4.8 here.  It works a charm.

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

Re: [GENERAL] Postgres + CentOS

[Apu Islam]

Just installed and tuned 8.0. tested with 100 connections and a 7.5 GB database, works like a champ.
new pl/perl is a beauty to write triggers on 8.0, check it out.
 
On 11/2/05, Steve Wampler <[EMAIL PROTECTED]> wrote:
Hugo wrote:> Hi, is anybody using psql and CentOS, I just wanted to know your experienceWorks just fine, though we're still on 
7.4 and haven't tried the 8 seriesyet.  I don't expect any problems, however.--Steve Wampler -- [EMAIL PROTECTED]The gods that smiled on your birth are now laughing out loud.
---(end of broadcast)---TIP 3: Have you checked our extensive FAQ?  http://www.postgresql.org/docs/faq

Re: [GENERAL] Disappearing Records

[Martijn van Oosterhout]

On Wed, Nov 02, 2005 at 10:28:56AM -0500, Tom Lane wrote:
> Looking at the code, I think that actually a regular, non-FREEZE VACUUM
> would do the "right thing" for tuples up to about 1 billion xacts past
> wrap, which is probably enough.  So the answer may be "just VACUUM".
> I'm still too lazy to test it though.

Well, I tested on 8.1beta which gets downright obnoxious about
wraparound. It refuses to do anything unless you're running in a
standalone backend. Anyway, your theory seems correct, for this version
anyway. The xmin is changed to FrozenTransactionId so it will be
visible forever more.

By plan or by accident, this certainly helps those people who run into
this, as long as previous versions work like this also...

Oh, the large positive numbers seems odd to me. I would have thought:

WARNING:  database "test" must be vacuumed within -49 transactions

would be clearer (and more alarming) than:

WARNING:  database "test" must be vacuumed within 4294967247 transactions

Have a nice day,

test=# insert into test values (1);
INSERT 0 1
test=# select ctid, xmin, xmax, cmin, cmax, * from test;
 ctid  | xmin  | xmax | cmin | cmax | value 
---+---+--+--+--+---
 (0,1) | 20138 |0 |0 |0 | 1
(1 row)

test=# \q

-- resetxlog to a new value, create the clog so you can actually start

backend> select ctid, xmin, xmax, cmin, cmax, * from test;
WARNING:  database "template1" must be vacuumed within 45 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in 
"template1".
 1: ctid(typeid = 27, len = 6, typmod = -1, byval = f)
 2: xmin(typeid = 28, len = 4, typmod = -1, byval = t)
 3: xmax(typeid = 28, len = 4, typmod = -1, byval = t)
 4: cmin(typeid = 29, len = 4, typmod = -1, byval = t)
 5: cmax(typeid = 29, len = 4, typmod = -1, byval = t)
 6: value   (typeid = 23, len = 4, typmod = -1, byval = t)

 1: ctid = "(0,1)"  (typeid = 27, len = 6, typmod = -1, byval = f)
 2: xmin = "20138"  (typeid = 28, len = 4, typmod = -1, byval = t)
 3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
 4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
 5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
 6: value = "1" (typeid = 23, len = 4, typmod = -1, byval = t)

backend> insert into test values (2);
WARNING:  database "template1" must be vacuumed within 44 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in 
"template1".
 1: value   (typeid = 23, len = 4, typmod = -1, byval = t)

backend> select ctid, xmin, xmax, cmin, cmax, * from test;
WARNING:  database "template1" must be vacuumed within 43 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in 
"template1".
 1: ctid(typeid = 27, len = 6, typmod = -1, byval = f)
 2: xmin(typeid = 28, len = 4, typmod = -1, byval = t)
 3: xmax(typeid = 28, len = 4, typmod = -1, byval = t)
 4: cmin(typeid = 29, len = 4, typmod = -1, byval = t)
 5: cmax(typeid = 29, len = 4, typmod = -1, byval = t)
 6: value   (typeid = 23, len = 4, typmod = -1, byval = t)

 1: ctid = "(0,1)"  (typeid = 27, len = 6, typmod = -1, byval = f)
 2: xmin = "20138"  (typeid = 28, len = 4, typmod = -1, byval = t)
 3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
 4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
 5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
 6: value = "1" (typeid = 23, len = 4, typmod = -1, byval = t)

 1: ctid = "(0,2)"  (typeid = 27, len = 6, typmod = -1, byval = f)
 2: xmin = "2147484102" (typeid = 28, len = 4, typmod = -1, byval = t)
 3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
 4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
 5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
 6: value = "2" (typeid = 23, len = 4, typmod = -1, byval = t)


-- It's complaining about template1, so just out and fix them first
-- After coming back, it's decided it's already wrapped around, even
-- though I did a VACUUM FREEZE before adding the values. The really
-- large numbers are misleading also.

backend> insert into test values(3);
WARNING:  database "test" must be vacuumed within 4294967248 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "test".
 1: value   (typeid = 23, len = 4, typmod = -1, byval = t)

backend> select ctid, xmin, xmax, cmin, cmax, * from test;
WARNING:  database "test" must be vacuumed within 4294967247 transactions
HINT:  To avoid a database shutdown, execute a full-da

Re: [GENERAL] Disappearing Records

[Tom Lane]

Martijn van Oosterhout  writes:
> On Wed, Nov 02, 2005 at 09:46:38AM -0500, Tom Lane wrote:
>> You sure about that?  I think VACUUM just tests for "committed or not".
>> 
>> I'm too lazy to set up a test case, but it's possible that VACUUM FREEZE
>> would resurrect wrapped-around tuples, or could be made to with only a
>> small code tweak.

> Well, that would be really nice to be able to tell people. But looking
> at the code of HeapTupleSatisfiesVacuum it spends a lot of time
> checking hint bits. While we might be able to fiddle the one function,
> checking all the places involving the hint bits could be nasty. Or not.

You're missing the forest for the trees.  The hint bits don't do
anything except save a visit to pg_clog.  It's still going to come back
with HEAPTUPLE_LIVE.  The question is whether VACUUM can or should be
tweaked to substitute FrozenTransactionId when the xmin is "in the
future".

Looking at the code, I think that actually a regular, non-FREEZE VACUUM
would do the "right thing" for tuples up to about 1 billion xacts past
wrap, which is probably enough.  So the answer may be "just VACUUM".
I'm still too lazy to test it though.

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] Postgres + CentOS

[Steve Wampler]

Hugo wrote:
> Hi, is anybody using psql and CentOS, I just wanted to know your experience

Works just fine, though we're still on 7.4 and haven't tried the 8 series
yet.  I don't expect any problems, however.


-- 
Steve Wampler -- [EMAIL PROTECTED]
The gods that smiled on your birth are now laughing out loud.

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

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

Re: [GENERAL] Disappearing Records

[Martijn van Oosterhout]

On Wed, Nov 02, 2005 at 09:46:38AM -0500, Tom Lane wrote:
> Martijn van Oosterhout  writes:
> > The really nasty thing about it is that because the records are now
> > considered really old, as soon as you do run VACUUM it'll start
> > removing the rows you want to save...
> 
> You sure about that?  I think VACUUM just tests for "committed or not".
> 
> I'm too lazy to set up a test case, but it's possible that VACUUM FREEZE
> would resurrect wrapped-around tuples, or could be made to with only a
> small code tweak.

Well, that would be really nice to be able to tell people. But looking
at the code of HeapTupleSatisfiesVacuum it spends a lot of time
checking hint bits. While we might be able to fiddle the one function,
checking all the places involving the hint bits could be nasty. Or not.

Making a test case would be interesting, I'll look into it sometime I
guess...

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpIKdfnMAQTM.pgp
Description: PGP signature

Re: [GENERAL] Disappearing Records

[Tom Lane]

Martijn van Oosterhout  writes:
> The really nasty thing about it is that because the records are now
> considered really old, as soon as you do run VACUUM it'll start
> removing the rows you want to save...

You sure about that?  I think VACUUM just tests for "committed or not".

I'm too lazy to set up a test case, but it's possible that VACUUM FREEZE
would resurrect wrapped-around tuples, or could be made to with only a
small code tweak.

regards, tom lane

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

[GENERAL] Postgres + CentOS

[Hugo]

Hi, is anybody using psql and CentOS, I just wanted to know your experience 

thanks 

Hugo

Re: [GENERAL] Disappearing Records

[Douglas McNaught]

Rory Browne <[EMAIL PROTECTED]> writes:

> select u.username, g.groupname from users u, groups g where u.group_id=g.id
> (assuming users are in exactly one group)
>
> If the group_id field in the users table was corrupted, and set to a
> value that isn't in the groups table, then that view wouldn't return
> anything.

That's why foreign key constraints are good.  :)

-Doug

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

Re: [GENERAL] Oracle 10g Express - any danger for Postgres?

[Ian Harding]

On 11/1/05, Andrew Rawnsley <[EMAIL PROTECTED]> wrote:
>
> They actually did make _some_ strides. The installer actually works
> consistently (knock on veneer-covered-pressboard), which is something I
> haven't seen since the pre-8i text-mode installs...
>
> Doesn't quite compare to the 5 minute untar/config/build/install/create
> database cycle we're used to with PG however.
>
Or the ten second rpm -i...

I've only ever installed Oracle once, but I was offended by the
REQUIREMENT of a gui to install the thing.  I managed to just install
the X client and connect from another machine, but still, that seems
whack to me.  Plus I had to run a command line "installer" for the
particular Linux flavor that "prepared" the system for me to install
Oracle.  Yikes.  I don't see a single MySQL fanboi going through that
if their biggest beef about PostgreSQL (besides how PAINFULLY slow it
is ;^) is how horribly difficult it is to install and configure.

Having said all that, I will probably dabble in it since it fattens up
the resume...

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

Re: [GENERAL] Backend connection pooling memleak

[Martijn van Oosterhout]

On Tue, Nov 01, 2005 at 11:33:39PM -0700, Mike Shelton wrote:
> Hello,
> 
> I implemented connection pooling in the backend of postgresql (one of the
> TODO items) and I've found some really interesting performance improvements
> I'd like to continue to explore but unfortunately I've also uncovered a
> nasty little side effect with the memory usage.  It's my guess that it's
> either a number of mallocs don't have corresponding frees because the
> process would exit when the transaction is done (normally) or I've bypassed
> some of the memory context "free"s that would have also resulted in memory
> being reclaimed on a process exit or more importantly on a connection being
> reclosed.

You should probably look at the memory management documentation. Many
(most?) pmallocs are not explicitly freed. Instead, the context they
are allocated in is either reset or freed. So in your case, it should
simply be a matter of resetting some of the nearly top level contexts.

See src/backend/utils/mmgr/README

> The implementation was done on 7.3.4.  It was the latest at the time I
> started and I haven't sync'd up yet because I wasn't sure if there would be
> a need in terms of the general postgresql population wanting this feature.
> If the improvements prove to be real and generalized versus specialized
> (just a few use cases for example) then I would imagine the interest would
> grow but I'm not there yet.

The question is, are the improvements worth the costs? What does your
implementation do that something like pgpool doesn't?

Good luck.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpTYPBxJZyCR.pgp
Description: PGP signature

Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.1.0 Release Candidate 1

[Dave Page]

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> Marc G. Fournier
> Sent: 31 October 2005 04:48
> To: pgsql-announce@postgresql.org
> Cc: pgsql-general@postgresql.org
> Subject: [ANNOUNCE] PostgreSQL 8.1.0 Release Candidate 1 
> 
> 
> After a couple of months of testing, and alot of bug reports 
> (with fixes), 
> we are pleased to announce the first Release Candidate of PostgreSQL 
> 8.1.0.
> 
> As with all pre-releases, but especially now that we are in the final 
> stretch, testing is paramount to a successful, and bug free, 
> release.  As 
> such, we ask everyone able who is able to do so to, to run 
> RC1 through its 
> paces and report any bugs to us through [EMAIL PROTECTED]
> 
> At this time, our plans for full release are the week of 
> November 7th, but 
> this depends on the success of this Release Candidate.
> 
> To download via FTP, please go to:
> 
>   http://www.postgresql.org/ftp/source/v8.1beta
> 
> For RPMs, please visit:
> 
>   http://developer.postgresql.org/~devrim/rpms/8.1/rc1
> 
> Windows Binaries to be announced shortly.

Windows binaries are now available from

http://www.postgresql.org/ftp/binary/v8.1beta/win32/

Apologies for the delay.

Dave Page
PostgreSQL Core Team

---(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] looking for multi-language app example using postgresql

[YL]

I tried but have very little progress on this. If 
any one know where to find an
multi-language example (php prefered) using 
postgresql, please let me know.
Thanks
[EMAIL PROTECTED]

[GENERAL] md5 hash on table row

[Jon Lapham]

Hello all,

I would like a generic way to generate an md5 hash for each row of a 
table.  Currently I do it thusly:


select id, md5(col_a || col_b || col_c || col_d) from mytable;
  id  |   md5
--+--
 1| 75acee3133f19d1a81ab2e7c1c32eb29
 2| 496f5e8bc945a922fcdd487e1ddde5c5
 3| ace10f4b1408d179da2e93267b300108
 4| bd029a826a98c21ec4c3661cc34657f8
 5| 4bacd2b0f34213a32f911ed5c1240c09

As you can see, I place each field inside the md5() function call. 
This, however, requires that I know what the table fields are, and it is 
not very robust (ie: boolean and any other column type that can't use 
"||" concatenation fails).  Is there a better, robust, or more generic 
way to do this?  I have tables with columns of type bytea, boolean, 
binary, etc... all the difficult column types.


I would love something like this:
select id, md5(*) from mytable;

...which of course does not work.

Thanks for any ideas!
-Jon

--
-**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*---
 Jon Lapham  <[EMAIL PROTECTED]>Rio de Janeiro, Brasil
 Personal: http://www.jandr.org/
***-*--**---***---


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

[GENERAL] Backend connection pooling memleak

[Mike Shelton]

Hello,

I implemented connection pooling in the backend of postgresql (one of the
TODO items) and I've found some really interesting performance improvements
I'd like to continue to explore but unfortunately I've also uncovered a
nasty little side effect with the memory usage.  It's my guess that it's
either a number of mallocs don't have corresponding frees because the
process would exit when the transaction is done (normally) or I've bypassed
some of the memory context "free"s that would have also resulted in memory
being reclaimed on a process exit or more importantly on a connection being
reclosed.

The implementation is different from front-end pooling in that the client
does not keep their connection open with the database.  The socket is closed
for example.  Also a basic authorization check (to make sure the client
credentials match those of an existing pooled connection) happens.  So the
connection does get some of the initialization that occurs for a new
connection (but not all, a lot of it can be skipped because it has already
been setup).

Anyway, my hope is there might be someone who is familiar with either the
memory system or the connection startup and shutdown process enough that
could provide a few pointers as to where I could start looking.  I've been
instrumenting the code to track malloc/pmalloc and free/pfree calls to see
which ones don't match for example but it's a bit laborious and would be
simpler if I could narrow down where I think things might be having
problems.

The implementation was done on 7.3.4.  It was the latest at the time I
started and I haven't sync'd up yet because I wasn't sure if there would be
a need in terms of the general postgresql population wanting this feature.
If the improvements prove to be real and generalized versus specialized
(just a few use cases for example) then I would imagine the interest would
grow but I'm not there yet.

I can get you copies of the changes I've made.  I changed 27 files (some of
the changes are minor, some more complicated) and I added 2 files as well
(for the pooling functionality).

Thank you,
Mike



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

Re: [GENERAL] Disappearing Records

[Rory Browne]

Thanks guys for your suggestions, but the problem turned out to be my
lack of experience(with PostgreSQL), combined with a bug in our PHP
Code.

Coming from a MySQL background, I assumed that if you "select x from
y", then y would be the name of a table. It turned out that in the
case that y was actually a view. It has a complex defination, but for
explanitory sake, lets say it was defined as:

select u.username, g.groupname from users u, groups g where u.group_id=g.id
(assuming users are in exactly one group)

If the group_id field in the users table was corrupted, and set to a
value that isn't in the groups table, then that view wouldn't return
anything.

Something like that(except that our view wasn't quite as simple) happened to me.

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

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

Re: [GENERAL] Oracle 10g Express - any danger for Postgres?

[Andrew Rawnsley]

They actually did make _some_ strides. The installer actually works
consistently (knock on veneer-covered-pressboard), which is something I
haven't seen since the pre-8i text-mode installs...

Doesn't quite compare to the 5 minute untar/config/build/install/create
database cycle we're used to with PG however.

On 11/1/05 8:49 AM, "Tom Lane" <[EMAIL PROTECTED]> wrote:

> Jan Wieck <[EMAIL PROTECTED]> writes:
>> On 10/31/2005 1:14 PM, Chris Browne wrote:
>>> The fact that it appears "a joke" to people wanting to deploy big
>>> databases doesn't prevent it from taking a painful bite out of, oh,
>>> say, certain vendors that forgot to own their own transactional
>>> storage engine...
> 
>> It's not a joke. It fits exactly the "small web application" needs. Who
>> will want to pay for a commercial MySQL license when they can run Oracle
>> for free?
> 
> People who can't figure out how to configure Postgres are not likely to
> get far with Oracle ;-).  Unless Oracle has made some *huge* strides in
> ease of installation/administration with 10g, I see this making
> practically no dent in MySQL.  Or PG for that matter.  All they're
> really likely to accomplish is to cannibalize some of their own low-end
> sales.
> 
> 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

-- 

Andrew Rawnsley
Chief Technology Officer
Investor Analytics, LLC
(740) 587-0114
http://www.investoranalytics.com




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

   http://archives.postgresql.org

Re: [GENERAL] PostgreSQL on 64-bit operating systems

[Brent Wood]

On Tue, 1 Nov 2005, Craig wrote:

> Hi
>
> I am going to be hosting a PostgreSQL database on a new server. We will be 
> purchasing a server with the AMD Athlon 64 3200+ processor.
> We are now posed with a choice of "FreeBSD 5.4" or "FreeBSD 5.4 x86_64Bit". 
> My question is: Will PostgreSQL 8.04 run perfectly on a 64-bit OS, or should 
> I run the 32 bit OS for now?
>
> Any help will be greatly appreciated

I'm running 8.03 on SuSE 9.3 A64, compiled from source for a 64 bit
package. It seems fine, no problems over the 4 weeks or so I've had it
running. About a 5Gb database, with heavy Postgis use.

See http://www.anandtech.com/IT/showdoc.aspx?i=2447&p=4

for some 32/64bit database benchmarks, unfortunately, not Postgres, but it
may still be of interest.


Brent Wood


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

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

Re: [GENERAL] Copying data from one column to another

[Nels Lindquist]

On 2 Nov 2005 at 0:34, Tino Wildenhain wrote:

> Am Dienstag, den 01.11.2005, 16:03 -0700 schrieb Nels Lindquist:
> > Hi there.
> > 
> > Sorry if this is a silly question, I'm relatively new to PostgreSQL.
> > 
> > I'm trying to copy information from one column, modify it and place 
> > it in another column.  Is there some shortcut way to do this with an 
> > UPDATE statement?
> 
> Yes, this is simply UPDATE.
> 
> UPDATE sometable SET somecolumn=somefunction(someothercolumn);

Thanks!  Knowing that it *should* work, I was able to figure out what 
I was doing wrong (casting to a data type which didn't match the 
array type I'd accidentally selected when creating the column with 
pgAdmin III).


Nels Lindquist <*>
Information Systems Manager
Morningstar Air Express Inc.


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

[GENERAL] Clustered indexes - When to use them?

[MaXX]

Hi,
Is there any "rule of thumb" on when to (not) use clustered indexes?
What appen to the table/index? (any change on the physical organisation?)
I've seen speed improvement on some queries but I'm not sure if I must use
them or not...

My rows are imported in batch of 100 (once the main script has collected
them, this takes between 1 and 30min), then another script vacuums the
table and aggregate the last imported rows,  if I add a column with the
commit timestamp and cluster on it, will I gain some perfs or not?

Thanks,
-- 
MaXX


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

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

[GENERAL] Replicating databases

[Carlos Benkendorf]

Hello,
 
Currently our company has a lot of small stores distributed around the country and in the actual database configuration we have a central database and all the small stores accessing it remotely.
 
All primary key tables were designed with a column identifying the store that it belongs. In other words, the store that can update the line, other stores can read it but the system was designed in such a way that other stores can not update information that do not belong to them.
 
The performance is not good because the line speed that connects the store to the central database sometimes is overloaded. We´re thinking to replicate the central database to each store. The store would be able to read all the information from the local database but should only update lines that belong to that store. 
 
When a store needs read information about other stores, it is not necessary to be updated, it can be a yesterday snapshot.
 
During the night all the local store databases will be consolidated in only one database and replicated again to the stores. In the morning, when the store opens, the local database has an updated and consolidated data.
I would appreciate suggestions about how the best way to implement such soluction.
 
Slony-1? SQL scripts?
 
Thanks in advance!
Benkendorf__Faça ligações para outros computadores com o novo Yahoo! Messenger http://br.beta.messenger.yahoo.com/ 

Re: [GENERAL] Disappearing Records

[Martijn van Oosterhout]

On Wed, Nov 02, 2005 at 05:00:35PM +0530, Venki wrote:
> >The really nasty thing about it is that because the records are now
> >considered really old, as soon as you do run VACUUM it'll start
> >removing the rows you want to save...

> So does this mean that when we do a vacuum for the first time there will
> still be data loss or Am I wrong in this?

VACUUM cannot recover data from transaction wraparound. But we havn't
even determined if this has happened as you have not yet posted the
output of this query:

SELECT datname, age(datfrozenxid) FROM pg_database;

Have a ncie day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpRIx3Uw1KEs.pgp
Description: PGP signature

Re: [GENERAL] PostgreSQL, Mac OS X and locales

[Guido Neitzer]

On 02.11.2005, at 11:47 Uhr, Martijn van Oosterhout wrote:


Ah you noticed that. Yes, many of the UTF-8 locales in FreeBSD simply
point to the ASCII versions which doesn't exactly work very well.


Right.

Only another FreeBSD system, these files are not portable. However,  
the

source files for these are plain text so you can edit them. I beleive
the source is in the C library. Find the source files, edit them and
recompile. Then just copy the LC_COLLATE file over.


I have to look how to do this. I have build locales a very long time  
ago, but currently I'm looking where to start ... perhaps with the  
Darwin sources. We will see.



You might also want to look at some of the other predefined locale
collate orders, maybe someone has put some effert in getting an order
right for another language.


I tried a lot of them and the all didn't work for me.

cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Disappearing Records

[Venki]

Hi,
 

>The really nasty thing about it is that because the records are now
>considered really old, as soon as you do run VACUUM it'll start
>removing the rows you want to save...
 
 
So does this mean that when we do a vacuum for the first time there will still be data loss or Am I wrong in this?
 
 
regards
Venki 
 
---Original Message---
 

From: Martijn van Oosterhout
Date: 11/02/05 17:09:45
To: John Sidney-Woollett
Cc: Venki; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Disappearing Records
 
On Wed, Nov 02, 2005 at 07:40:29AM +, John Sidney-Woollett wrote:
> If you have suffered data loss for this reason, then you'll need to get
> help from the developers to see whether it can be recovered, or what you
> can do to reconstruct the data.
 
The really nasty thing about it is that because the records are now
considered really old, as soon as you do run VACUUM it'll start
removing the rows you want to save...
 
What you need is a tool to go through and change any transaction ID
significantly in the future and freeze it... You know, like VACUUM...
Oh well..
 
In 8.1 autovacuum will make this kind of thing obsolete.
 
Have a nice day.
--
Martijn van Oosterhout      http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
 
 

Re: [GENERAL] mysql replace in postgreSQL?

[Tzvetan Tzankov]

without unique constraint even mysql replace doesnot work as expected

Jan Wieck wrote:

On 10/31/2005 11:58 AM, Lincoln Yeoh wrote:


At 08:24 AM 10/30/2005 -0800, David Fetter wrote:

> 
>http://developer.postgresql.org/docs/postgres/plpgsql-control-structure 
s.html#PLPGSQL-ERROR-TRAPPING

>
> Erm, doesn't it have the same race conditions?

No, don't believe it does.  Have you found some?



Depends on how you do things.

As I mentioned, it's only fine if you have the relevant uniqueness 
constraint.



One would use MySQL's REPLACE INTO to avoid duplicates. To deliberately 
omit the UNIQUE constraint in order to make the stored procedure 
solution fail would smell a lot like the old MySQL crashme BS ... first 
create and drop 10,000 tables to bloat the system catalog, next vacuum 
with a user that doesn't have privileges to vacuum system catalogs 
(because we told them to vacuum after that silly crap test), then show 
that the system is still slow.


Using REPLACE INTO at one place and creating duplicates on purpose in 
another seems to make zero sense to me. Until one can explain the reason 
for that to me, I claim that a UNIQUE constraint on such key is a 
logical consequence.



Jan



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

  http://archives.postgresql.org

Re: [GENERAL] Disappearing Records

[Martijn van Oosterhout]

On Wed, Nov 02, 2005 at 07:40:29AM +, John Sidney-Woollett wrote:
> If you have suffered data loss for this reason, then you'll need to get 
> help from the developers to see whether it can be recovered, or what you 
> can do to reconstruct the data.

The really nasty thing about it is that because the records are now
considered really old, as soon as you do run VACUUM it'll start
removing the rows you want to save...

What you need is a tool to go through and change any transaction ID
significantly in the future and freeze it... You know, like VACUUM...
Oh well..

In 8.1 autovacuum will make this kind of thing obsolete.

Have a nice day.
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpmpU3bcHqm6.pgp
Description: PGP signature

Re: [GENERAL] PostgreSQL, Mac OS X and locales

[Martijn van Oosterhout]

On Wed, Nov 02, 2005 at 07:27:49AM +0100, Guido Neitzer wrote:
> The LC_COLLATE for this locale is a link pointing to "../la_LN.US- 
> ASCII/LC_COLLATE". This is why I don't think they paid much attention  
> to the correct sort order of umlauts.

Ah you noticed that. Yes, many of the UTF-8 locales in FreeBSD simply
point to the ASCII versions which doesn't exactly work very well.

> But I have also tried ISO-5589-, ISO-8859-15 and others. No change.  
> May I take an LC_COLLATE file from another system and use this?

Only another FreeBSD system, these files are not portable. However, the
source files for these are plain text so you can edit them. I beleive
the source is in the C library. Find the source files, edit them and
recompile. Then just copy the LC_COLLATE file over.

You might also want to look at some of the other predefined locale
collate orders, maybe someone has put some effert in getting an order
right for another language.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpG5s7AKFJRw.pgp
Description: PGP signature