[GENERAL] Database separation for backup

[Nagalingam, Karthikeyan]

Hi,
 
Is there any way to keep each database in separate partition ie)
separate folder for each database. 
 
I would like to do the dabase level backup in storage side, for that If
I am able to separate the database in folderwise, I can allocate
separate partition for each database. Then I can take the backup in each
partition. 
 
Your recommendation and suggestion are welcome. 
 
Regards 
Karthikeyan.N
 
 

Re: [GENERAL] Database separation for backup

[Ashish Karalkar]

Nagalingam, Karthikeyan wrote:

Hi,
 
Is there any way to keep each database in separate partition ie) 
separate folder for each database.
 
I would like to do the dabase level backup in storage side, for that 
If I am able to separate the database in folderwise, I can allocate 
separate partition for each database. Then I can take the backup in 
each partition.
 
Your recommendation and suggestion are welcome.
 
Regards

Karthikeyan.N
 
 
I guess tablespace will be of  your use and also you can directly use  
softlink to link database directory in data/base  to the 
desired folder.



With Regards
--Ashish

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

Re: [GENERAL] Database separation for backup

[Karsten Hilbert]

On Thu, Mar 12, 2009 at 04:18:50PM +0530, Nagalingam, Karthikeyan wrote:

> Is there any way to keep each database in separate partition ie)
> separate folder for each database. 
>  
> I would like to do the dabase level backup in storage side, for that If
> I am able to separate the database in folderwise, I can allocate
> separate partition for each database. Then I can take the backup in each
> partition. 


You can use tablespaces to separate things on disk but it
won't make taking filesystem level backups as easy as you
might hope.

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

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

Re: [GENERAL] Database separation for backup

[Nagalingam, Karthikeyan]

Thanks karsten.

Anybody have production setup with diff database in diff folder ie) diff
partition. Please provide the details if possible help me with diaghram.


Regards 
Karthikeyan.N
 

-Original Message-
From: Karsten Hilbert [mailto:karsten.hilb...@gmx.net] 
Sent: Thursday, March 12, 2009 4:46 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Database separation for backup

On Thu, Mar 12, 2009 at 04:18:50PM +0530, Nagalingam, Karthikeyan wrote:

> Is there any way to keep each database in separate partition ie) 
> separate folder for each database.
>  
> I would like to do the dabase level backup in storage side, for that 
> If I am able to separate the database in folderwise, I can allocate 
> separate partition for each database. Then I can take the backup in 
> each partition.


You can use tablespaces to separate things on disk but it won't make
taking filesystem level backups as easy as you might hope.

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

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

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

[GENERAL] How to get the PID associated with a Perl DBI dbh?

[Kynn Jones]

Is there a reliable way to find out the (Unix) PID associated with a
database handle generated by Perl DBI's database connection?
TIA!

Kynn

Re: [GENERAL] How to get the PID associated with a Perl DBI dbh?

[A. Kretschmer]

In response to Kynn Jones :
> Is there a reliable way to find out the (Unix) PID associated with a database
> handle generated by Perl DBI's database connection?

You can ask the pg_stat_activity - View, column procpid.


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

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

Re: [GENERAL] How to get the PID associated with a Perl DBI dbh?

[Joshua Tolley]

On Thu, Mar 12, 2009 at 03:13:13PM +0100, A. Kretschmer wrote:
> In response to Kynn Jones :
> > Is there a reliable way to find out the (Unix) PID associated with a 
> > database
> > handle generated by Perl DBI's database connection?
> 
> You can ask the pg_stat_activity - View, column procpid.

The query "select pg_backend_pid()" issued through that handle will also
return the PID.

- Josh / eggyknap


signature.asc
Description: Digital signature

Re: [GENERAL] How to get the PID associated with a Perl DBI dbh?

[Martin Gainty]

pg clients communicate on 5432 so a simple search on 5432 will yield the pid 
e.g.
netstat -aon | grep 5432

Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 




Date: Thu, 12 Mar 2009 09:59:56 -0400
Subject: [GENERAL] How to get the PID associated with a Perl DBI dbh?
From: kyn...@gmail.com
To: pgsql-general@postgresql.org

Is there a reliable way to find out the (Unix) PID associated with a database 
handle generated by Perl DBI's database connection?
TIA!
Kynn

_
Windows Live™ Groups: Create an online spot for your favorite groups to meet.
http://windowslive.com/online/groups?ocid=TXT_TAGLM_WL_groups_032009

Re: [GENERAL] How to get the PID associated with a Perl DBI dbh?

[A. Kretschmer]

In response to Martin Gainty :
> pg clients communicate on 5432 so a simple search on 5432 will yield the pid
> e.g.
> netstat -aon | grep 5432

Thats no useful:

- you can't do that from the client (across the network)
- you can't see which pid has a particular client


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

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

[GENERAL] nulls

[James B. Byrne]

I am confronting a design decision involving null values and I
cannot seem to discern which way to go.  Therefore, I would like
some commentary based on real world experience.

The basic issue is episodic duration, expressed as columns named
dt_effective_from and dt_superseded_after.  Both are datetime types
containing values normalized to utc.  You see where this is going.

The issue is what to enter when the value is known to be unknown, as
in some indeterminate future date, which may be never.  I read that
relational set values should never be null, as null is indeterminate
for WHERE clauses and may result in unexpected results.  On the
other hand, setting some artificially excessive future date seems in
its place seems, to me, to have its own problems.

Since this issue must have been dealt with time and time again in
the past I would like to know what, if any, consensus has been
reached on the matter.  What is the best way to proceed?

-- 
***  E-Mail is NOT a SECURE channel  ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3


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

Re: [GENERAL] How to get the PID associated with a Perl DBI dbh?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> Is there a reliable way to find out the (Unix) PID associated with a
> database handle generated by Perl DBI's database connection?

$dbh->{pg_pid}

- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200903121148
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkm5LwgACgkQvJuQZxSWSsgojQCgyqfH1SyJx+8H3rDXmnoea560
T0AAn2Av9W9Edmt8aNBaV4caN1rScH2O
=GoEE
-END PGP SIGNATURE-



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

Re: [GENERAL] nulls

[Richard Huxton]

James B. Byrne wrote:
> The basic issue is episodic duration, expressed as columns named
> dt_effective_from and dt_superseded_after.  Both are datetime types
> containing values normalized to utc.  You see where this is going.
> 
> The issue is what to enter when the value is known to be unknown, as

If it's unknown use null.

> in some indeterminate future date, which may be never. 

That's not unknown that's "in the future".

> I read that
> relational set values should never be null, as null is indeterminate
> for WHERE clauses and may result in unexpected results.

Only if you use it to mean something other than unknown. If you have an
event that starts '2001-01-01 01:01:01+01' and ends "null" then you can
confidently say "don't know" as to how long that event is.

>  On the
> other hand, setting some artificially excessive future date seems in
> its place seems, to me, to have its own problems.

Which is where you reach for the handy datetime literals as described below:
 select 'infinity'::timestamp without time zone;
 select '-infinity'::timestamp without time zone;

-- 
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] nulls

[justin]

Both ways will work.

Setting the superseded_after to a far off future will work but will have 
to set to a real date when it actual does become superseded.


The same is true for nulls  It boils down to how you and the users want 
to look at the data.


To me to get the all the records that has not been superseded yet is 
simple either way. 


Select * from some_table where superseded_after IS NULL

or
Select * from some_table where superseded_after > current_date

James B. Byrne wrote:

I am confronting a design decision involving null values and I
cannot seem to discern which way to go.  Therefore, I would like
some commentary based on real world experience.

The basic issue is episodic duration, expressed as columns named
dt_effective_from and dt_superseded_after.  Both are datetime types
containing values normalized to utc.  You see where this is going.

The issue is what to enter when the value is known to be unknown, as
in some indeterminate future date, which may be never.  I read that
relational set values should never be null, as null is indeterminate
for WHERE clauses and may result in unexpected results.  On the
other hand, setting some artificially excessive future date seems in
its place seems, to me, to have its own problems.

Since this issue must have been dealt with time and time again in
the past I would like to know what, if any, consensus has been
reached on the matter.  What is the best way to proceed?

  


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

Re: [GENERAL] I don't want to back up index files

[Alan Hodgson]

On Wednesday 11 March 2009, Glen Parker  wrote:
> We have yet to recover from a PG disaster.  We back up every night, and
> never use the back ups for anything.  To me, it seems perfectly
> reasonable to get a quicker back up every night, with the remote
> possibility of ever having to pay the price for it.  

This isn't totally relevant, but as a way to speed up base backups, I keep a 
copy of the whole database rsync'd on the backup machine. The base backup 
consists of refreshing the rsync, and then tarring it offline (of course 
with the PITR backup commands in the sequence). My database is currently 
750GB on disk and gets many tens of millions of updates a day, and the 
rsync still runs in less than an hour per night.

I have done PITR recoveries (unfortunately). The log replay time dwarfs the 
time it takes to copy the index files back over (it is, honestly, slower 
than the original transactions were). Additionally, copying them is faster 
than rebuilding them would be. Also, I can't imagine bringing a database 
online without the majority of the indexes in place. The first dozen 
non-indexed queries against large tables would kill the machine; not only 
would you not be servicing users, but the rest of your restore would be 
slowed immensely.

-- 
Even a sixth-grader can figure out that you can’t borrow money to pay off 
your debt

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

Re: [GENERAL] nulls

[Bill Moran]

In response to "James B. Byrne" :
> 
> The basic issue is episodic duration, expressed as columns named
> dt_effective_from and dt_superseded_after.  Both are datetime types
> containing values normalized to utc.  You see where this is going.
> 
> The issue is what to enter when the value is known to be unknown, as
> in some indeterminate future date, which may be never.  I read that
> relational set values should never be null, as null is indeterminate
> for WHERE clauses and may result in unexpected results.

Where'd you read that?  There are very specific rules for how NULL
behaves within WHERE clauses.  I expect that whoever wrote what you
read didn't know those rules, or wrote them based on a DB system that's
less well documented than PostgreSQL.

Of course, if you don't know the rules, you don't know what to expect,
thus the results can be unexpected.

> On the
> other hand, setting some artificially excessive future date seems in
> its place seems, to me, to have its own problems.

IMHO, the use of magic values should have been deprecated in the 70s.
NULL means NULL, infinity means infinity.  Trying to use NULL to mean
anything other than "no value provided" is begging for trouble.  Putting
magic values in like "infinity in this field means that we have not
yet calculated the value" or something similar is equally problematic.

> Since this issue must have been dealt with time and time again in
> the past I would like to know what, if any, consensus has been
> reached on the matter.  What is the best way to proceed?

Depends on what you're trying to accomplish.  However, the most
unambiguous way to solve the problem is to solve it unambiguously:
create a BOOLEAN column called superseded with a default
value of false, and create a trigger that sets it to true when
a value is inserted in dt_superseded_after.  You can now explicitly
filter on that column to avoid any unexpected NULLishness, and your
queries will read more like English than some constructed inside-
joke language.

Compare:

SELECT * FROM duration WHERE dt_superseded_after IS NULL;

to

SELECT * FROM duration WHERE NOT superseded;

In a larger, more complex query the difference in readability will
be even more marked.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

Re: [GENERAL] nulls

[Jeff Davis]

On Thu, 2009-03-12 at 11:32 -0400, James B. Byrne wrote: 
> The basic issue is episodic duration, expressed as columns named
> dt_effective_from and dt_superseded_after.  Both are datetime types
> containing values normalized to utc.  You see where this is going.

I assume you're concern is NULL for the "superseded_after" field and not
the "effective_from" field.

Specifying an "unknown" superseded_after date is not really expressing
what you really want to express. First of all, if it is not superseded,
that is not the same thing as "it has been superseded, I just don't know
when it was superseded". Second, even if you know that it will be
superseded at some point in the future, you know (at a minimum) that
will be superseded beyond the effective_from date, so "unknown" is not
expressing everything that you do know.

An approach you might consider is horizontal partitioning, that is, use
two tables, one for episodes that are complete, and another for episodes
that are in progress. This will allow you to express everything that you
do know, and it's also convenient for writing queries that are easier to
understand. Additionally, it has the nice property that queries on
in-progress data will be more efficient.

You can use NULLs if there's some reason you want to combine the two
tables, but then you have to be careful to not use NULLs for other
purposes (e.g. be careful when using outer joins or NULL to really mean
"unknown" or "not applicable" in some other sense). In other words, if
you do use NULL, you are actually imparting more meaning on NULL than it
would ordinarily have, so you have to be careful not to confuse things.

I found the book "Temporal Data and the Relational Model" by C.J. Date,
et al., very helpful. There's a section about this problem specifically
in the book, but I don't have it nearby so I don't have a page number.

Regards,
Jeff Davis




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

Re: [GENERAL] Get IP addresses from tsvectors

[Lubomir Petrov]

Hi,

Maybe you can use something like the following:

test=# select * from t1;
   t
---
 'leas':4 'return':2
 'leas':2 'found':1 'address':5 'hardwar':4 '65.110.236.113':6
 '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'free':14 'leas':15
 'martin':12 'network':11 'dhcpdiscov':1 '10.10.94.126':10
 'leas':2 'found':1 'address':5 'request':4 '65.110.236.113':6
 'ip':4 'leas':2,5 'ident':7 'hardwar':1
 'leas':2 'choos':1 'address':5 'request':4
 'leas':2 'return':1 '65.110.236.113':3
 '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'dhcpdiscov':1
 '10.10.94.126':10
 '00':5 '17':6 '1d':9 '27':8 '4c':10 'f2':7 'via':11 'dhcpoffer':1
 '10.10.94.126':12 '65.110.236.113':3
 '451':6 'tri':9 '4.7.1':7 'later':11 'pleas':8 'milter':2 'reject':5
 'tempfail':12 'n29c3q08020087':1 'kgan...@iastate.edu':4
(14 rows)

test=#
test=#
test=# select
test-#distinct ip_address
test-# from
test-#(select substring(t from E'\\d+\\.\\d+\\.\\d+\\.\\d+') as 
ip_address from t1) as t

test-# where ip_address is not null;
   ip_address

 10.10.94.126
 65.110.236.113
(2 rows)


test=#
test=#


Of course you should make the regular expression stricter, but this is 
the idea.


Hope that helps.


Regards,
Lubomir Petrov


Justin Funk wrote:

Greetings,

I have a table with a column with type tsvector. It contains the
result of to_tsvector() of varchar field in the table. What I'd like
to do is be able to search through the table and find all of the
distinct IP addresses. Any idea how to turn:

SELECT message_index_col FROM systemevents LIMIT 10;
message_index_col
-
 'leas':4 'return':2
 'leas':2 'found':1 'address':5 'hardwar':4 '65.110.236.113':6
 '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'free':14 'leas':15
'martin':12 'network':11 'dhcpdiscov':1 '10.10.94.126':10
 'leas':2 'found':1 'address':5 'request':4 '65.110.236.113':6
 'ip':4 'leas':2,5 'ident':7 'hardwar':1
 'leas':2 'choos':1 'address':5 'request':4
 'leas':2 'return':1 '65.110.236.113':3
 '00':3 '17':4 '1d':7 '27':6 '4c':8 'f2':5 'via':9 'dhcpdiscov':1
'10.10.94.126':10
 '00':5 '17':6 '1d':9 '27':8 '4c':10 'f2':7 'via':11 'dhcpoffer':1
'10.10.94.126':12 '65.110.236.113':3
 '451':6 'tri':9 '4.7.1':7 'later':11 'pleas':8 'milter':2 'reject':5
'tempfail':12 'n29c3q08020087':1 'kgan...@iastate.edu':4

into

IP_ADDRESSES
-
65.110.236.113
10.10.94.126

Thanks for the help...

Justin Funk




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

[GENERAL] Postgresql

[André Silva]

Hi
I've installed the postgresql in my computer but i've forgotten my password.
i've tried everything, i've been in your page that requires a e-mail to send 
information to reset the password but i never get the e-mail so 
far.(http://www.postgresql.org/community/lostpwd)
i've tried to make a new username but the ones that i've tried already exist.
What can i do? format my pc?
tell me something because i really need this

Best regards
André Silva

Re: [GENERAL] Postgresql

[Raymond O'Donnell]

On 12/03/2009 16:51, André Silva wrote:

> I've installed the postgresql in my computer but i've forgotten my
> password.

Do you mean the password for the super-user account in PostgreSQL itself
(usually "postgres")? - or the password for the user account on the
machine under which PostgreSQL runs?

If it's the password for the PG superuser, and assuming that you've only
just installed PG and have no databases yet that you want to keep, you
could just scrub the installation and start again; do an initdb, or if
you're on Windows, uninstall and reinstall.

If you need to keep your installation, you could try what was suggested
in this post:

  http://archives.postgresql.org/pgsql-general/2008-12/msg00700.php

If it's the user account password, a privileged user can reset it...what
OS are you on?

> i've tried everything, i've been in your page that requires a e-mail
> to send information to reset the password but i never get the e-mail
> so far.(http://www.postgresql.org/community/lostpwd)

That's not what you need - that's for community accounts which let you
do stuff on the the PostgreSQL website, wiki, etc.

> i've tried to
> make a new username but the ones that i've tried already exist. What
> can i do? format my pc?

Ouch! Definitely not that! :-)

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

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

Re: [GENERAL] Postgresql

[Guy Rouillier]

André Silva wrote:

Hi
I've installed the postgresql in my computer but i've forgotten my password.
i've tried everything, i've been in your page that requires a e-mail to 
send information to reset the password but i never get the e-mail so 
far.(http://www.postgresql.org/community/lostpwd)
i've tried to make a new username but the ones that i've tried already 
exist.

What can i do? format my pc?
tell me something because i really need this


Well, you certainly don't need to reformat your PC.  You didn't provide 
much information that would enable people to help you, such as the 
version of PostgreSQL, what if any customization you have already done, 
if you've put things in your database you need access to, etc.  In the 
absence of all that, probably the quickest way for you to get access to 
a database would be to simply create a new one.


A more descriptive subject to your emails would also encourage people to 
respond.


--
Guy Rouillier

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

Re: [GENERAL] Postgresql

[Andreas Kretschmer]

André Silva  wrote:

> Hi
> I've installed the postgresql in my computer but i've forgotten my password.
> i've tried everything, i've been in your page that requires a e-mail to send

What have you tried exactly? And what error-messages you got?


> information to reset the password but i never get the e-mail so far.(http://
> www.postgresql.org/community/lostpwd)

This is for the email-list, not for the access to your db...


> i've tried to make a new username but the ones that i've tried already exist.
> What can i do? format my pc?

Depends.

> tell me something because i really need this

You can change (edit) your pg_hba.conf, setting the auth-method to
trust. But this is only a guess, because i don't know what you have
tried exactly and i don't know the error-message you got, never even
your operating system...


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

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

Re: [GENERAL] Postgresql

[Rich Shepard]

On Thu, 12 Mar 2009, Andreas Kretschmer wrote:


I've installed the postgresql in my computer but i've forgotten my password.
i've tried everything, i've been in your page that requires a e-mail to send



i've tried to make a new username but the ones that i've tried already exist.
What can i do? format my pc?


Depends.


  If the OP is running linux (or a *BSD), he can su to root and replace the
password for the preferred user (postgres, probably). The passwd program
will allow this. If the machine runs Microsoft, I've no idea at all since we
don't use any of that.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [GENERAL] I don't want to back up index files

[Glen Parker]

Tom Lane wrote:

Glen Parker  writes:

Tom Lane wrote:

... AFAICS what
Glen is proposing is to not WAL-log index changes, and with that any
crash no matter how minor would have to invalidate indexes.



Nooo...!  This has nothing to do with WAL logging index changes.


How so?  In any PITR-based situation it seems to me you need to worry
about the WAL bulk a lot more than the bulk of the base backup.



It isn't the bulk so much as the amount of time, and the impact to the 
running system during that time, that it takes to execute the base backup.


I haven't noticed any real impact related to compressing and exporting 
WAL files.


Anyway, more to the point, I'm not knowingly proposing anything that 
should cause reduced system reliability in the event of a crash.


-Glen


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

Re: [GENERAL] I don't want to back up index files

[Glen Parker]

Tom Lane wrote:

Glen Parker  writes:
We have yet to recover from a PG disaster.  We back up every night, and 
never use the back ups for anything.  To me, it seems perfectly 
reasonable to get a quicker back up every night, with the remote 
possibility of ever having to pay the price for it.


Why don't you just switch to a less frequent full-backup schedule?



Paranoia.  Others in the organization have pushed to keep up with the 
nightly back ups, so that decision is mostly out of my hands.


-Glen


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

Re: [GENERAL] Postgresql

[Raymond O'Donnell]

On 12/03/2009 19:56, Rich Shepard wrote:

> will allow this. If the machine runs Microsoft, I've no idea at all

Start Menu
  -> Administrative Tools
-> Computer Management
  -> Local Users & Groups
-> Users

...then right-click on the user, and select "Set password".

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

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

Re: [GENERAL] I don't want to back up index files

[Joshua D. Drake]

On Thu, 2009-03-12 at 12:59 -0700, Glen Parker wrote:
> Tom Lane wrote:
> > Glen Parker  writes:
> >> We have yet to recover from a PG disaster.  We back up every night, and 
> >> never use the back ups for anything.  To me, it seems perfectly 
> >> reasonable to get a quicker back up every night, with the remote 
> >> possibility of ever having to pay the price for it.
> > 
> > Why don't you just switch to a less frequent full-backup schedule?
> 
> 
> Paranoia.  Others in the organization have pushed to keep up with the 
> nightly back ups, so that decision is mostly out of my hands.

Why not just take a backup of the pitr slave instead? If you need to do
it nightly, shut down the standby process, tar, start standby process.
You never have to  bother the master at all.

Sincerely,

Joshua D. Drake


> 
> -Glen
> 
> 
-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


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

Re: [GENERAL] I don't want to back up index files

[Glen Parker]

Scott Marlowe wrote:

That's two people now who have called the idea "silly" without even a hint
of a supporting argument.  Why would it be "silly" to improve the
performance of a highly valuable tool set without compromising its utility?


Because it's the size of the WAL files that kills most people, and not
putting the index updates into WAL files would be a hack I wouldn't
trust, and having them on the otherside but not adding them is just
wasting space?  Cause maybe, you didn't explain everything as clearly
as you could, and I made assumptions based on your incomplete
description?


I've re-read my original email and I just can't see how anybody got the 
idea I was suggesting to not WAL record index changes.  That would be 
insanity IMO.


So, to be clear, I'm not proposing any change to the way data is written 
to the WAL files.


-Glen


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

Re: [GENERAL] Postgresql

[John R Pierce]

Raymond O'Donnell wrote:

On 12/03/2009 19:56, Rich Shepard wrote:

  

will allow this. If the machine runs Microsoft, I've no idea at all



Start Menu
  -> Administrative Tools
-> Computer Management
  -> Local Users & Groups
-> Users

...then right-click on the user, and select "Set password".
  


or, at a Windows command line prompt,  (of course, replacing newpassword 
with whatever random password you chose)


   C:\>  net user postgres newpassword

then make sure the pgsql-8.3 (or whatever) service descriptor is using 
this same password...


   C:\> net user postgres newpassword
   The command completed successfully.

   C:\> sc config pgsql-8.3 password= mumba$$
   [SC] ChangeServiceConfig SUCCESS

   C:\> net start pgsql-8.3
   The PostgreSQL Database Server 8.3 service is starting.
   The PostgreSQL Database Server 8.3 service was started successfully.


(note on the SC CONFIG command, the spacing is critical on that 
password= ... there's no space before the = and there must be a space 
after it, quirky little thing)




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

Re: [GENERAL] I don't want to back up index files

[Alvaro Herrera]

Glen Parker escribió:

> I've re-read my original email and I just can't see how anybody got the  
> idea I was suggesting to not WAL record index changes.

The subject says that.


> That would be  insanity IMO.

Agreed :-)

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

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

[GENERAL] pg_standby error - can't find 00000001.history

[Eric Soroos]

Hi,

I'm setting up a replacement standby server.  I've had this working  
before until the old standby server lost a drive array and a  
motherboard. So, my process was working. On the other hand, the old  
slave was debian etch, with a backported 8.2 release, and the new one  
is ubuntu 8.04.


I've been able to bring the database up from pitr replication using a  
simple cp restore command. So I'm not concerned that I can't get the  
DB up, or that the format of the data is wrong.


What's not working is doing this in a warm standby manner with  
pg_standby.


postg...@grape:/home/erics$ pg_ctlcluster 8.2 main start
The PostgreSQL server failed to start. Please check the log output:
2009-03-12 13:40:18 PDT LOG:  could not load root certificate file  
"root.crt": no SSL error reported

2009-03-12 13:40:18 PDT DETAIL:  Will not verify client certificates.
2009-03-12 13:40:18 PDT LOG:  database system was interrupted at  
2009-03-11 16:31:37 PDT

2009-03-12 13:40:18 PDT LOG:  starting archive recovery
2009-03-12 13:40:18 PDT LOG:  restore_command = "/usr/lib/postgresql/ 
8.2/bin/pg_standby -l -d -k 100 -r 2 -s 2 -w 0 -t /tmp/pgsql.trigger. 
5432 /data/pg/repl-db3 %f %p 2>> standby.log"
2009-03-12 13:40:18 PDT FATAL:  could not restore file  
"0001.history" from archive: return code 32512
2009-03-12 13:40:18 PDT LOG:  startup process (PID 6223) exited with  
exit code 1
2009-03-12 13:40:18 PDT LOG:  aborting startup due to startup process  
failure

postg...@grape:/home/erics$ tail /data/pg/main/standby.log
sh: /usr/lib/postgresql/8.2/bin/pg_standby: not found
sh: /usr/lib/postgresql/8.2/bin/pg_standby: not found
sh: /usr/lib/postgresql/8.2/bin/pg_standby: not found
sh: /usr/lib/postgresql/8.2/bin/pg_standby: not found
postg...@grape:/home/erics$ ls -l /usr/lib/postgresql/8.2/bin/pg_standby
-rwxr-xr-x 1 root root 20028 2009-03-12 11:44 /usr/lib/postgresql/8.2/ 
bin/pg_standby

postg...@grape:/home/erics$ ls -l /data/pg/repl-db3
 ls -l /data/pg/repl-db3
total 2723068
-rw--- 1 postgres postgres 16777216 2009-03-09 21:22  
0001004E00EC
-rw--- 1 postgres postgres 16777216 2009-03-09 22:22  
0001004E00ED
-rw--- 1 postgres postgres 16777216 2009-03-09 23:22  
0001004E00EE

...

1) What is 0001.history? It doesn't look like a WAL file.
2) What file is not found?  It sorta looks like the pg_standby  
binary, but I'm not sure that I believe that.
3) I know that it's going to ask for files that aren't found. Why is  
it failing this time?
4) The process I'm following did work on a pair of debian-etch  
machines. We managed to fail over and reset the spare at least 25 times.

I'm concerned that I don't understand why it's failing.

Any ideas?

thanks.

eric



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

Re: [GENERAL] nulls

[James B. Byrne]

On Thu, March 12, 2009 12:00, Richard Huxton wrote:
> James B. Byrne wrote:
>> The basic issue is episodic duration, expressed as columns named
>> dt_effective_from and dt_superseded_after.  Both are datetime
>> types containing values normalized to utc.
>
> If it's unknown use null.
>
>> in some indeterminate future date, which may be never.
>
> That's not unknown that's "in the future".
>
> If you have an event that starts '2001-01-01 01:01:01+01' and
> ends "null" then you can confidently say "don't know" as to how
> long that event is.
>
>> On the other hand, setting some artificially excessive future
>> date seems in its place seems, to me, to have its own problems.
>
> Which is where you reach for the handy datetime literals as
> described below:
>  select 'infinity'::timestamp without time zone;
>  select '-infinity'::timestamp without time zone;
>

Ahh. I did not know about infinity as a value for datetime columns. 
This I will investigate further.

For brevity's sake, and because I was focused on my own situation, I
did not give sufficient context for what is happening.  This issue
relates to taxation. In the regime I must deal with, taxation rates
are often set some considerable time before they come into effect. 
They are also sometimes related to specific commodities for specific
times of the year.

For example, tomato forcings may be taxed at 5% from 20090101T0500Z
until 20090401T0359Z, then at 0% from 20090401T0400Z to ? (until the
minister decides to withdraw the preferential rate).

Sometime in January we may, in fact we will, know when the
preferential rate will come into effect. However, sometimes not even
the ministry itself knows when this will be revoked and the regular
tax rate reinstated.  On the other hand, there are instances when
the preferential treatment period is delimited from the outset so
that the effective and the superseded date are both known well in
advance.  The point being is that the necessary database updates can
be input well in advance of their being used.

Because of these real world constraints I do not see how we could
use a Boolean flag to indicate whether a rate is active or not. 
What rate is in effect is entirely dependent upon the transaction
date and that date on occasion may be considerably in the past.  So
we are forced to determine the effective rate with a conditional
(assuming that we use null to mean unset) along the lines of:

select c.e_tx_rate
  from commodity_tax_rates c
  where c.commodity_class = 
and c.date_effective_from <= 
and
   (c.date_superseded_after IS NULL
or
c.date_superseded_after <= )

Please forgive any syntax errors. This is obviously not a piece of
real code, for one thing the selection criteria involves far more
than commodity class and transaction_date. It serves but to
demonstrate one approach to the problem.

I infer that if instead we set the unknown superseded date to
infinity (or some arbitrary large date far in the future) then the
query is simplified to:

...
and c.date_effective_from >= 
and c.date_superseded_after <= 

Have I understood things aright?


-- 
***  E-Mail is NOT a SECURE channel  ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3


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

[GENERAL] Question about Privileges

[Jack W]

Assume that I have the following database:

Database:mydb
Schema:  mydb_schema
Tables:mydb_table1
   mydb_table2
   mydb_table3

I create a role: Create Role dbuser LOGIN;

mydb=# grant all privileges on mydb_schema.mydb_table1 to dbuser;
mydb=# grant all privileges on SCHEMA mydb_schema to dbuser;

Remotely connect dbuser to mydb and run SELECT:
mydb=#select * from mydb_schema.mydb_table1;

Here I want to do a SELECT on mydb_table1 as the above line shows. It seems
to me that I have to grant Privileges to dbuser on both the  mydb_schema and
mydb_table1 so that the dbuser can run SELECT.
If there are 10 tables under mydb_schema, do I need to grant privileges to
the dbuser  on all the 10 tables individully so that dbuser can operate the
10 tables?

I also find that if I only grant privileges on database to dbuser as below,
without granting privileges on Schema and table to dbuser, dbuser still can
not do SELECT on the tables.
mydb=# grant all privileges on Database mydb to dbuser;

Is there any simple way to grant All privileges to dbuser on all the 10
tables?

Thanks.

Jack

Re: [GENERAL] nulls

[A.M.]

On Mar 12, 2009, at 5:50 PM, James B. Byrne wrote:


...
   and c.date_effective_from >= 
   and c.date_superseded_after <= 

Have I understood things aright?


The one problem I foresee is that changes to the commodity_tax_rates  
table may not reflect in transaction dates that have passed. What  
happens if a tax is retroactively ended or applied outside these  
barriers? Is this tax calculation frozen at the date of the "best  
information we have"? If so, you might consider an insert-only table  
and linking the tax decision to the row that happened to be in effect  
at any time. This would likely require adding a column indicating when  
the row was inserted.


Cheers,
M

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

Re: [GENERAL] Question about Privileges

[John R Pierce]

Jack W wrote:


I also find that if I only grant privileges on database to dbuser as 
below, without granting privileges on Schema and table to dbuser, 
dbuser still can not do SELECT on the tables.

mydb=# grant all privileges on Database mydb to dbuser;

Is there any simple way to grant All privileges to dbuser on all the 
10 tables?


the simplest way is to make dbuser the OWNER of the database, and have 
him create all the tables too, then he automatically has full rights to it.


there are also various scripts that can be used to enumerate objects and 
grant specific privileges...


   http://pgedit.com/public/sql/acl_admin/index.html
   http://unf.be/postgresql/postgres_grant_all.perl
   http://www.archonet.com/pgdocs/grant-all.html

in general, DATABASE privileges relate to connecting to that database, 
and having the rights to create schemas in that database.


SCHEMA privileges grant the rights to connect to a schema, and 
create/modify schemas


table/view/etc privileges grant the rights to select/insert/update/etc 
the mentioned tables.


see http://www.postgresql.org/docs/current/static/sql-grant.html for 
more specifics.


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

Re: [GENERAL] pg_standby error - can't find 00000001.history

[Tom Lane]

Eric Soroos  writes:
> 2009-03-12 13:40:18 PDT LOG:  starting archive recovery
> 2009-03-12 13:40:18 PDT LOG:  restore_command = "/usr/lib/postgresql/ 
> 8.2/bin/pg_standby -l -d -k 100 -r 2 -s 2 -w 0 -t /tmp/pgsql.trigger. 
> 5432 /data/pg/repl-db3 %f %p 2>> standby.log"
> 2009-03-12 13:40:18 PDT FATAL:  could not restore file  
> "0001.history" from archive: return code 32512
> 2009-03-12 13:40:18 PDT LOG:  startup process (PID 6223) exited with  
> exit code 1

Hmm ... 32512 is 0x7F00, which I think means exit(127), which is
generally what the shell returns when it can't find the program
it's supposed to execute.

> sh: /usr/lib/postgresql/8.2/bin/pg_standby: not found
> sh: /usr/lib/postgresql/8.2/bin/pg_standby: not found
> sh: /usr/lib/postgresql/8.2/bin/pg_standby: not found
> sh: /usr/lib/postgresql/8.2/bin/pg_standby: not found

This seems to square with the above conclusion.

> 2) What file is not found?  It sorta looks like the pg_standby  
> binary, but I'm not sure that I believe that.

Permissions problems on some containing directory, perhaps?

regards, tom lane

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

Re: [GENERAL] I don't want to back up index files

[Tom Lane]

Glen Parker  writes:
> I've re-read my original email and I just can't see how anybody got the 
> idea I was suggesting to not WAL record index changes.

Mainly because the idea doesn't seem to make sense unless that's part
of the package.  If you don't cut index changes out of the WAL load
then the savings on the base backup alone aren't going to be all that
exciting when you consider the total cost of PITR backup.

Furthermore, you would need some very ugly hacks on the recovery process
to make it ignore (rather than try to apply) WAL records relating to
indexes.  I believe there are a fair number of cases where the recovery
process doesn't even know that a particular file is an index, because
the WAL stream doesn't tell it.  The live backends generating the WAL
log entries typically know that (and could suppress the entries) but the
recovery process has only a very limited view of reality.  It cannot,
for example, trust the system catalogs to be in a correct/consistent
state, so it couldn't look up the info for itself.

BTW, there's a related problem with the idea, which is that the
tools normally used to take base backups haven't got any way to
distinguish indexes from any other kind of relation.

regards, tom lane

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

Re: [GENERAL] I don't want to back up index files

[Tom Lane]

Alan Hodgson  writes:
> I have done PITR recoveries (unfortunately). The log replay time dwarfs the 
> time it takes to copy the index files back over (it is, honestly, slower 
> than the original transactions were).

Yeah :-( ... the problem is that recovery is serialized while the
original transactions might have run in parallel.  There is work
going on to improve that, but I don't know how much better it's
likely to get.

regards, tom lane

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

Re: [GENERAL] I don't want to back up index files

[Glen Parker]

Tom Lane wrote:

Glen Parker  writes:
Mainly because the idea doesn't seem to make sense unless that's part
of the package.  If you don't cut index changes out of the WAL load
then the savings on the base backup alone aren't going to be all that
exciting when you consider the total cost of PITR backup.


In our setting, I think it might be more exciting than you think.  As I 
said, I've not noticed any real impact to the system related to WAL 
exporting, but the nightly backup does indeed have a significant impact 
because of how long it runs.  WAL export is a couple seconds ever few 
minutes, which nobody ever notices.  The backup runs for a minimum of an 
hour and fifteen minutes, which people definitely notice.



Furthermore, you would need some very ugly hacks on the recovery process
to make it ignore (rather than try to apply) WAL records relating to
indexes.  I believe there are a fair number of cases where the recovery
process doesn't even know that a particular file is an index, because
the WAL stream doesn't tell it.  The live backends generating the WAL
log entries typically know that (and could suppress the entries) but the
recovery process has only a very limited view of reality.  It cannot,
for example, trust the system catalogs to be in a correct/consistent
state, so it couldn't look up the info for itself.


Could the live backends label the log entries with "hints" to be used by 
the replay process?  In this case, I would think a simple flag 
indicating whether replay is critical or not would suffice.



BTW, there's a related problem with the idea, which is that the
tools normally used to take base backups haven't got any way to
distinguish indexes from any other kind of relation.


Yes there's no doubt it would increase the complexity of the base 
backup, IF a person chooses to ignore indexes.  The up side is that 
people who are happy with the backup as it is would have to do nothing 
at all, it would just continue to work as it does now.  To ignore 
indexes (and only certain indexes at that), you'd have to examine the 
system catalog as part of each backup.  I already do that to some 
extent, in order to discover all the extra tablespaces that need to be 
backed up.


I guess the biggest problem I see with this is that it would have rather 
a small target audience.



-Glen


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

Fwd: [GENERAL] Question about Privileges

[Jack W]

On Thu, Mar 12, 2009 at 3:45 PM, John R Pierce  wrote:

> Jack W wrote:
>
>>
>> I also find that if I only grant privileges on database to dbuser as
>> below, without granting privileges on Schema and table to dbuser, dbuser
>> still can not do SELECT on the tables.
>> mydb=# grant all privileges on Database mydb to dbuser;
>>
>> Is there any simple way to grant All privileges to dbuser on all the 10
>> tables?
>>
>
> the simplest way is to make dbuser the OWNER of the database, and have him
> create all the tables too, then he automatically has full rights to it.
>


Thanks. In my application, one user will create the database, then other
users can remotely access it through ODBC/JDBC to access the database. In
this case, I have to grant the privileges to each user one by one, right?



>
> SCHEMA privileges grant the rights to connect to a schema, and
> create/modify schemas
>
> table/view/etc privileges grant the rights to select/insert/update/etc the
> mentioned tables.
>
>
> In my test as below:
mydb=#select * from mydb_schema.mydb_table1;

If I only grant the privileges to the table:
mydb=# grant all privileges on mydb_schema.mydb_table1 to dbuser;

The SELECT will fail, the error is:

STATEMENT:  select * from mydb_schema.mydb_table1;
ERROR: permission denied for schema mydb_schema

So I have to grant the privileges to the schema mydb_schema in order to do
SELECT on mydb_table1.

Jack

Re: [GENERAL] pg_standby error - can't find 00000001.history

[Eric Soroos]

2) What file is not found?  It sorta looks like the pg_standby
binary, but I'm not sure that I believe that.


Permissions problems on some containing directory, perhaps?


It's the same directory as the postgresql binaries, and they all have  
sane
permissions.  (root:root, 755).  Ls finds it, more finds it but  
complains

as it's a binary.  (all run as the postgres user)

The only think that I can think is that somehow the shell is  
considering it

a corrupted shebang, and can't fine the corrupted path to execute.

I've pulled in the packaged 8.3 binaries for this distro, and it appears
that the pg_standby in that package is working properly.

thanks,

eric




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

[GENERAL] text column indexing in UTF-8 database

[Reece Hart]

Do I really need 4 indexes per column to handle the 4 combinations of
{equality,like/regexp} x {unfolded,casefolded} in a UTF-8 encoded
database?

I have a column that I'd like to be able to search with equality and
regexp (or like), optionally casefolded. The database is UTF-8 encoded.
The table and index defs are below.

Jeff Davis gave me a tip to use text_pattern_ops on indexes to speed up
regexp and like; that worked beautiful. But I discovered a caveat that
t_p_o apparently doesn't handle equality. Thus, I think I need distinct
indexes for the 4 cases above. Right?

Thanks,
Reece


r...@csb-dev=> \d pannotation
   Table "unison.pannotation"
 Column |   Type   | Modifiers  
+--+
 pannotation_id | integer  | not null default 
 origin_id  | integer  | not null
 alias  | text | not null
 descr  | text | 
 tax_id | integer  | 
 added  | timestamp with time zone | not null default timenow()
Indexes:
...
"pannotation_alias" btree (alias)
"pannotation_alias_cf" btree (lower(alias))
"pannotation_alias_cf_tpo" btree (lower(alias) text_pattern_ops)
"pannotation_alias_tpo" btree (alias text_pattern_ops)
...

where those indexes are defined as:
r...@csb-dev=> \x
r...@csb-dev=> select indexname,indexdef from pg_indexes
where indexname~'^pannotation_alias';
-[ RECORD 1 ]
indexname | pannotation_alias_cf_tpo
indexdef  | CREATE INDEX pannotation_alias_cf_tpo ON pannotation USING btree 
(lower(alias) text_pattern_ops)
-[ RECORD 2 ]-
indexname | pannotation_alias_tpo
indexdef  | CREATE INDEX pannotation_alias_tpo ON pannotation USING btree 
(alias text_pattern_ops)
-[ RECORD 3 ]-
indexname | pannotation_alias
indexdef  | CREATE INDEX pannotation_alias ON pannotation USING btree (alias)
-[ RECORD 4 ]-
indexname | pannotation_alias_cf
indexdef  | CREATE INDEX pannotation_alias_cf ON pannotation USING btree
(lower(alias))


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


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

Re: Fwd: [GENERAL] Question about Privileges

[Adrian Klaver]

On Thursday 12 March 2009 5:00:39 pm Jack W wrote:
> On Thu, Mar 12, 2009 at 3:45 PM, John R Pierce  wrote:
> > Jack W wrote:
> >> I also find that if I only grant privileges on database to dbuser as
> >> below, without granting privileges on Schema and table to dbuser, dbuser
> >> still can not do SELECT on the tables.
> >> mydb=# grant all privileges on Database mydb to dbuser;
> >>
> >> Is there any simple way to grant All privileges to dbuser on all the 10
> >> tables?
> >
> > the simplest way is to make dbuser the OWNER of the database, and have
> > him create all the tables too, then he automatically has full rights to
> > it.
>
> Thanks. In my application, one user will create the database, then other
> users can remotely access it through ODBC/JDBC to access the database. In
> this case, I have to grant the privileges to each user one by one, right?

Easier to create a group role and assign it the privileges to the tables as you 
create them. Then as you create users assign them to the group.

>
> > SCHEMA privileges grant the rights to connect to a schema, and
> > create/modify schemas
> >
> > table/view/etc privileges grant the rights to select/insert/update/etc
> > the mentioned tables.
> >
> >
> > In my test as below:
>
> mydb=#select * from mydb_schema.mydb_table1;
>
> If I only grant the privileges to the table:
> mydb=# grant all privileges on mydb_schema.mydb_table1 to dbuser;
>
> The SELECT will fail, the error is:
>
> STATEMENT:  select * from mydb_schema.mydb_table1;
> ERROR: permission denied for schema mydb_schema
>
> So I have to grant the privileges to the schema mydb_schema in order to do
> SELECT on mydb_table1.
>
> Jack



-- 
Adrian Klaver
akla...@comcast.net

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

Re: [GENERAL] text column indexing in UTF-8 database

[Steve Atkins]

On Mar 12, 2009, at 5:15 PM, Reece Hart wrote:


Do I really need 4 indexes per column to handle the 4 combinations of
{equality,like/regexp} x {unfolded,casefolded} in a UTF-8 encoded
database?

I have a column that I'd like to be able to search with equality and
regexp (or like), optionally casefolded. The database is UTF-8  
encoded.

The table and index defs are below.

Jeff Davis gave me a tip to use text_pattern_ops on indexes to speed  
up

regexp and like; that worked beautiful. But I discovered a caveat that
t_p_o apparently doesn't handle equality. Thus, I think I need  
distinct

indexes for the 4 cases above. Right?


If A=B then lower(A) = lower(B), and if A like B then lower(A) like  
lower(B).


So, if nothing else, you could rewrite "where alias = 'Foo'" as
"where lower(alias) = lower('Foo') and alias='Foo'" and take advantage
of the lower() functional index.

I've read that 8.4 will be able to use a text_pattern_ops index for  
equality.


Cheers,
  Steve


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

Re: [GENERAL] text column indexing in UTF-8 database

[Jeff Davis]

On Thu, 2009-03-12 at 17:15 -0700, Reece Hart wrote:
> Jeff Davis gave me a tip to use text_pattern_ops on indexes to speed up
> regexp and like; that worked beautiful. But I discovered a caveat that
> t_p_o apparently doesn't handle equality. Thus, I think I need distinct
> indexes for the 4 cases above. Right?

It looks like an index using text_pattern_ops can be used for equality
(see my test case below).

This works apparently because texteq() is defined as bitwise-equality.
Is that really correct? I was under the impression that some locales do
not obey that rule, and may consider two slightly different strings to
be equal.

Regards,
Jeff Davis

create table a(t text);
create index a_idx on a (t text_pattern_ops);
insert into a values('foo');
set enable_seqscan='f';
analyze a;
explain analyze select * from a where t = 'foo';
   QUERY PLAN  
---
 Index Scan using a_idx on a  (cost=0.00..8.27 rows=1 width=4)
   (actual time=0.009..0.010 rows=1 loops=1)
   Index Cond: (t = 'foo'::text)
 Total runtime: 0.036 ms
(3 rows)



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

Re: [GENERAL] text column indexing in UTF-8 database

[Reece Hart]

On Thu, 2009-03-12 at 18:02 -0700, Jeff Davis wrote:
> It looks like an index using text_pattern_ops can be used for equality
> (see my test case below).

Odd. I can't reproduce your test case. I noticed that I edited out the
version and platform from my OP. (A: 8.3.6, x86_64 linux). You're on
8.3.6, or do you happen to be testing on the 8.4 branch?

I see this:

r...@rkh=> \i tpo-test.sql
version   

 PostgreSQL 8.3.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.0 (SUSE Linux)

select name,setting from pg_settings where name~'locale|encoding';
  name   | setting 
-+-
 client_encoding | UTF8
 server_encoding | UTF8
(2 rows)

\!echo $LANG
en_US.UTF-8

create temp table a(t text);
CREATE TABLE

insert into a values('foo');
INSERT 0 1

set enable_seqscan='f';
SET

create index a_t_tpo on a (t text_pattern_ops);
CREATE INDEX

analyze a;
ANALYZE

explain analyze select * from a where t = 'foo';
QUERY
PLAN 
---
 Seq Scan on a  (cost=1.00..10001.01 rows=1 width=4) (actual
time=0.014..0.016 rows=1 loops=1)
   Filter: (t = 'foo'::text)
 Total runtime: 0.047 ms
(3 rows)

create index a_t on a (t);
CREATE INDEX

analyze a;
ANALYZE

explain analyze select * from a where t = 'foo';
  QUERY
PLAN   
---
 Index Scan using a_t on a  (cost=0.00..8.27 rows=1 width=4) (actual
time=0.061..0.062 rows=1 loops=1)
   Index Cond: (t = 'foo'::text)
 Total runtime: 0.099 ms
(3 rows)


script at http://harts.net/reece/tpo-test.sql


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


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

Re: [GENERAL] text column indexing in UTF-8 database

[Reece Hart]

On Thu, 2009-03-12 at 17:32 -0700, Steve Atkins wrote:

> If A=B then lower(A) = lower(B), and if A like B then lower(A) like  
> lower(B).
> 
> So, if nothing else, you could rewrite "where alias = 'Foo'" as
> "where lower(alias) = lower('Foo') and alias='Foo'" and take advantage
> of the lower() functional index.


Good idea. Thanks. The niggling remaining problem is that the DB is open
to a SQL-savvy audience and it'd be nice to avoid telling them to
casefold their predicates.

For regexps, lower(alias) ~* lower(regexp) won't work because extended
regexps might contain character classes (e.g., \S != \s). And, I guess
that alias ~* regexp requires a seqscan because the index isn't ordered
over ~* (right?). How about lower(alias) ~* regexp ? Is PG smart enough
to know that that ordering is well defined? Is my head on straight
there?

Thanks again,
Reece

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