[GENERAL] phppgadmin

[Alain Roger]

Hi,I'm back on my problem with PHPpgadmin.. :-(when i log in for the first time, the server icon of servers list, is changing to Loadingand it is back on "not connected" server, but i've access to my database normally.
each time that i click on some items (create database, accounts, create tables,...), PHPpgadmin asks me again username and pwd.i've checked if my browsers allow cookies and sessions.. Everything is fine...
I also downgrade from apache 2.2.2 to apache 2.0.58 just in case of (based on my problem with php5apache2.dll issue in apache 2.2.2)Does anyone have some idea where could be located the problem ?I did the same configration at work that i have a home (from my network).
thanks a lotAlain

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Bruno Wolff III]

On Thu, Jun 29, 2006 at 14:27:30 +0200,
  Martijn van Oosterhout  wrote:
> On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> > The issue is the difference between start of transaction and time when
> > the serializable snapshot is taken. Since BEGIN and other commands may
> > be issued as separate network requests it makes sense to defer taking
> > the snapshot until the first time it is needed. The transaction is still
> > serializable, just that the manual is worded slightly incorrectly with
> > regards the exact timing.
> 
> I've always interpreted it as "there exists a serialised order for the
> transactions" but the database makes no guarentees about what it might
> be. I can't think of any real world case where you actually care about
> the order, just as long as one exists.

Postgres' serializable mode doesn't guaranty that. To get that effect you
may need to do some extra locking.

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

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Qingqing Zhou]

"Tom Lane" <[EMAIL PROTECTED]> wrote
>
> It can, but there are cases where you want the lock to be taken before
> the snapshot is set.  Otherwise, there could be committed changes in the
> database that you can't see in your snapshot.  I think there are some
> examples in the manual, or check the archives.
>

Ok, here are the links:

http://archives.postgresql.org/pgsql-bugs/2002-12/msg00169.php  - a bug
analysis of the take-snapshot at wrong point;

http://archives.postgresql.org/pgsql-hackers/2004-09/msg00133.php - recent
redesign notes.

Regards,
Qingqing



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

[GENERAL] finding gps within polygon

[blackwater dev]

Hello,Does anyone have a good tutorial on finding gps points within a polygon?  I need to be able to pass in a list of gps coordinates and let postgres return to me matching cities from my cities table that are within that polygon.
Thanks!

Re: [GENERAL] psql -h host ...

[Chris]

James wrote:

Thanks all for the reply.
Once pg_hba.conf is modified, do I have to run
pg_ctl reload ?


If in doubt check the documentation :)

http://www.postgresql.org/docs/8.1/static/client-authentication.html#AUTH-PG-HBA-CONF

"The pg_hba.conf file is read on start-up and when the main server 
process (postmaster) receives a SIGHUP signal. If you edit the file on 
an active system, you will need to signal the postmaster (using pg_ctl 
reload or kill -HUP) to make it re-read the file."


So yes.

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

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

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Tom Lane]

"Qingqing Zhou" <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> wrote
>> Right, the snapshot does not become set until you do a non-utility
>> command (normally, SELECT/INSERT/UPDATE/DELETE).  This is a feature, not
>> a bug, because it lets the transaction take table locks before its
>> snapshot becomes set.

> Hm, mostly I understand it as an optimization. What do you mean by "it lets
> the transaction take table locks before its snapshot becomes set"?  If we
> take a snapshot at BEGIN, then the transaction can't take table locks?

It can, but there are cases where you want the lock to be taken before
the snapshot is set.  Otherwise, there could be committed changes in the
database that you can't see in your snapshot.  I think there are some
examples in the manual, or check the archives.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Qingqing Zhou]

"Tom Lane" <[EMAIL PROTECTED]> wrote
>
> Right, the snapshot does not become set until you do a non-utility
> command (normally, SELECT/INSERT/UPDATE/DELETE).  This is a feature, not
> a bug, because it lets the transaction take table locks before its
> snapshot becomes set.
>

Hm, mostly I understand it as an optimization. What do you mean by "it lets
the transaction take table locks before its snapshot becomes set"?  If we
take a snapshot at BEGIN, then the transaction can't take table locks?

test=# begin;select count(*) from k;
BEGIN
 count
---
  
(1 row)

test=# lock table k;
LOCK TABLE

Regards,
Qingqing



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

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

Re: [GENERAL] User privileges in web database applications

[Tim Hart]

I'm coming in a bit late to this conversation, so forgive me if I've missed 
something. Isn't this problem the reason that connection pools were created?

In a connection pool, connections are only associated with a particular 
user for the duration of a transaction. Once the transaction is complete, 
the connection goes back to the pool.

Pool sizes are usually configurable, and the number of actual connections 
in the pool can be less than the configured amount. The pool is responsible 
for allocating blocks of connection - up to MAX - as necessary.

The pool is also responsible for guaranteeing that a connection is valid 
when it's requested from the pool. This may involve periodic 'pinging' of 
unused connections, or simply testing, and recreating if nessecary, a 
connection before it's reserved for use.

I'm pretty sure there is a pgpool project somewhere to provide exactly this 
service.

Tim
___
Sent with SnapperMail
www.snappermail.com

.. Original Message ...
On Thu, 29 Jun 2006 10:19:34 -0400 "John DeSoi" <[EMAIL PROTECTED]> wrote:
>
>On Jun 29, 2006, at 3:37 AM, Antonis Christofides wrote:
>
>> The benefit of this solution is that
>> I avoid reconnecting to the database on each request, having instead a
>> persistent connection as user "postgres".
>
>But it is also a resource liability. How do you know if the user will  
>make another request or when they are "finished" using your site? You  
>can certainly time out the connection but for a busy site you will  
>potentially be holding open a large number of connections that will  
>never be used again.
>
>I agree with your reasons for wanting to do this, but it may not be  
>feasible for a busy web site.


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

Re: [GENERAL] Database connectivity using a unix shell

[Richard Broersma Jr]

> What if I don't have a shell script and instead of that I have a C code and
> need to connect to the postgres database.
> How do i accomplish this? do i still need to call this psql clinet interface
> or there is some other way to do it..

In this case you could use ecpg:
http://www.postgresql.org/docs/8.1/interactive/ecpg.html

If you are interested in any other programming languages that can connect 
please see:
http://www.postgresql.org/download/
notice the section:
"Application Programming Interfaces"

Regards,

Richard Broersma Jr.


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

   http://archives.postgresql.org

Re: [GENERAL] Database connectivity using a unix shell

[Peter Wilson]

Are you just asking random questions? What do you actually want to do? You've
asked how to access Postres from a shell - now you're using 'C'. Are you going
to work your way through Java, Perl and a host of others.

All of this information is *very* clearly available in the manual at:
  http://www.postgresql.org/docs/8.1/interactive/index.html

It is an *excellent* manual. Apart from how to access Postgres in a multitude of
ways it includes a good reference on SQL itself.

Go have a read.

Jasbinder Bali wrote:
> What if I don't have a shell script and instead of that I have a C code
> and need to connect to the postgres database.
> How do i accomplish this? do i still need to call this psql clinet
> interface or there is some other way to do it..
> 
> ~Jas
> 
> On 6/29/06, *Scott Marlowe* <[EMAIL PROTECTED]
> > wrote:
> 
> On Thu, 2006-06-29 at 13:29, Jasbinder Bali wrote:
> > this is what i've included in my shell script
> >
> > query="select * from films";
> > a=`echo $query|psql -tq postgres`;
> >
> >
> > it gives an error:
> > role root doesn't exist.
> >
> > where do i have to specify the role?
> 
> OK, unless this script NEEDS to be run as root, it's a good idea to run
> it as an unprivaledged account.  Create a new one if you can.  If it
> has
> to be run as root, so be it.
> 
> You use -U, so it'd look like:
> 
> a=`echo $query|psql -tqU myname postgres`;
> 
> then you need a password in ~/.pgpass following the format I posted in
> the previous message, unless your server is running in trust mode, in
> which case you don't need to worry about the .pgpass file.
> 
> >
> > On 6/29/06, Scott Marlowe <[EMAIL PROTECTED]
> > wrote:
> > On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
> > > isn't my normal bash script different from psql.
> > > In a bash script how wud u specify the db parameters
> >
> > Look at how I'm doing it here:
> >
> > > > > query="select * from sometable";
> > > > > a=`echo $query|psql -tq dbname`;
> >
> > Note that I'm calling psql from within a bash script. So, the
> > connection
> > params are the same as for psql, cause that's what I'm using.
> >
> 
> 

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

   http://archives.postgresql.org

Re: [GENERAL] Database connectivity using a unix shell

[Jasbinder Bali]

What if I don't have a shell script and instead of that I have a C code and need to connect to the postgres database. How do i accomplish this? do i still need to call this psql clinet interface or there is some other way to do it..
~JasOn 6/29/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Thu, 2006-06-29 at 13:29, Jasbinder Bali wrote:> this is what i've included in my shell script>> query="select * from films";> a=`echo $query|psql -tq postgres`;>>
> it gives an error:> role root doesn't exist.>> where do i have to specify the role?OK, unless this script NEEDS to be run as root, it's a good idea to runit as an unprivaledged account.  Create a new one if you can.  If it has
to be run as root, so be it.You use -U, so it'd look like:a=`echo $query|psql -tqU myname postgres`;then you need a password in ~/.pgpass following the format I posted inthe previous message, unless your server is running in trust mode, in
which case you don't need to worry about the .pgpass file.>> On 6/29/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:> On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
> > isn't my normal bash script different from psql.> > In a bash script how wud u specify the db parameters>> Look at how I'm doing it here:>> > > > query="select * from sometable";
> > > > a=`echo $query|psql -tq dbname`;>> Note that I'm calling psql from within a bash script. So, the> connection> params are the same as for psql, cause that's what I'm using.
>

Re: [GENERAL] Database connectivity using a unix shell

[Chris Browne]

[EMAIL PROTECTED] ("Jasbinder Bali") writes:
> in my bash script where and how do i specify the database connection
> parameters

There are three ways:

1.  You can store them in environment variables:

PGDATABASE=my_database
PGPORT=8901
PGUSER=superman
PGHOST=some.host.somewhere
export PGDATABASE PGPORT PGUSER PGHOST

2.  You can specify them in command line parameters:

psql -h some.host.somewhere -p 8901 -U superman -d my_database

3.  You could specify a service name

PGSERVICE=my_fave_db psql

where the pg_service.conf in your PostgreSQL installation contains an
entry:

[my_fave_db]
dbname=my_database
user=superman
host=some.host.somewhere
port=8901

4.  You can mix these; perhaps start by having PGSERVICE provide part
of the information, then environment variables may add to/override
some of that, then command line parameters may add to/override
further...

For a lot of our production environments, we have set up a big,
comprehensive pg_service.conf file; local accounts then pick favorite
values for PGSERVICE...
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/lsf.html
"Why use Windows, since there is a door?"
-- <[EMAIL PROTECTED]> Andre Fachat

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

Re: [GENERAL] Database connectivity using a unix shell

[elein]

This is a variation of the same:

a=`psql -U postgres -h my.host.dom -Atc "select col1 from sometable where 
col2=6"  dbname`
echo $a

Note that the -U and -h can specify the user name and hostname if necessary.
-A gets the data unaligned
-t gets the data without the decorations
-c "query" specifies the query to run
dbname is required if it is not the default.

Also see psql --help 

elein

On Thu, Jun 29, 2006 at 11:40:49AM -0500, Scott Marlowe wrote:
> On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
> > isn't my normal bash script different from psql.
> > In a bash script how wud u specify the db parameters
> 
> Look at how I'm doing it here:
> 
> > > > query="select * from sometable"; 
> > > > a=`echo $query|psql -tq dbname`;
> 
> Note that I'm calling psql from within a bash script. So, the connection
> params are the same as for psql, cause that's what I'm using.
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 

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

Re: [GENERAL] Database connectivity using a unix shell

[Richard Broersma Jr]

> isn't my normal bash script different from psql.
> In a bash script how wud u specify the db parameters

This link might have something that you would like?
http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html

Regards,

Richard Broersma Jr.


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

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

Re: [GENERAL] Database connectivity using a unix shell

[Alan Hodgson]

On Thursday 29 June 2006 09:08, "Jasbinder Bali" <[EMAIL PROTECTED]> wrote:
> in my bash script where and how do i specify the database connection
> parameters

man psql

-- 
Alan

---(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] Database connectivity using a unix shell

[Scott Marlowe]

On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
> isn't my normal bash script different from psql.
> In a bash script how wud u specify the db parameters

Look at how I'm doing it here:

> > > query="select * from sometable"; 
> > > a=`echo $query|psql -tq dbname`;

Note that I'm calling psql from within a bash script. So, the connection
params are the same as for psql, cause that's what I'm using.

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

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

Re: [GENERAL] Database connectivity using a unix shell

[Jasbinder Bali]

isn't my normal bash script different from psql.
In a bash script how wud u specify the db parameters 
On 6/29/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Thu, 2006-06-29 at 11:08, Jasbinder Bali wrote:>> On 6/29/06, Scott Marlowe <
[EMAIL PROTECTED]> wrote:> On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:> > > Hi> > > I need to connect to the postgres database using my unix shell.> > > How should i go about it?
> > > Is libpq going to be helpful here?>> > There is a shell interface for postgresql called psql.>> > If you have postgresql installed on a box, then the psql interface
> > should be there.>> > If you need to access postgresql from within a bash script, you can> > do So with a construct kinda like this:>> > query="select * from sometable";
> > a=`echo $query|psql -tq dbname`;> in my bash script where and how do i specify the database connection> parametersIn psql you can use -U to specify your username.  for password info, you
need to use a .pgpass file (i.e. no passwords on the command line).http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html

Re: [GENERAL] Database connectivity using a unix shell

[Scott Marlowe]

On Thu, 2006-06-29 at 11:08, Jasbinder Bali wrote:
>  
> On 6/29/06, Scott Marlowe <[EMAIL PROTECTED]> wrote: 
> On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:
> > > Hi
> > > I need to connect to the postgres database using my unix shell. 
> > > How should i go about it?
> > > Is libpq going to be helpful here?
> 
> > There is a shell interface for postgresql called psql.
> 
> > If you have postgresql installed on a box, then the psql interface
> > should be there. 
> 
> > If you need to access postgresql from within a bash script, you can
> > do So with a construct kinda like this:
> 
> > query="select * from sometable";
> > a=`echo $query|psql -tq dbname`;

> in my bash script where and how do i specify the database connection
> parameters

In psql you can use -U to specify your username.  for password info, you
need to use a .pgpass file (i.e. no passwords on the command line).

http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html


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

Re: [GENERAL] Database connectivity using a unix shell

[Jasbinder Bali]

in my bash script where and how do i specify the database connection parameters
~Jas 
On 6/29/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:> Hi> I need to connect to the postgres database using my unix shell.
> How should i go about it?> Is libpq going to be helpful here?There is a shell interface for postgresql called psql.If you have postgresql installed on a box, then the psql interfaceshould be there.
If you need to access postgresql from within a bash script, you can doso with a construct kinda like this:query="select * from sometable";a=`echo $query|psql -tq dbname`;do a psql --help to get a list of options for psql.  -t means print only
the rows, no headers, and -q means to only display query output, nomessages or warnings.

Re: [GENERAL] Database connectivity using a unix shell

[Scott Marlowe]

On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:
> Hi
> I need to connect to the postgres database using my unix shell.
> How should i go about it?
> Is libpq going to be helpful here?

There is a shell interface for postgresql called psql.

If you have postgresql installed on a box, then the psql interface
should be there.

If you need to access postgresql from within a bash script, you can do
so with a construct kinda like this:

query="select * from sometable";
a=`echo $query|psql -tq dbname`;

do a psql --help to get a list of options for psql.  -t means print only
the rows, no headers, and -q means to only display query output, no
messages or warnings.

---(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] Database connectivity using a unix shell

[Richard Broersma Jr]

> I need to connect to the postgres database using my unix shell.
> How should i go about it?
> Is libpq going to be helpful here?

If postgres is install on the unix server, you can use the postgreSQL client 
that installs with
the server.

it is called psql.

Regards,

Richard Broersma Jr.

---(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] Database connectivity using a unix shell

[Jasbinder Bali]

Hi
I need to connect to the postgres database using my unix shell.
How should i go about it?
Is libpq going to be helpful here?
 
Thanks and regards,
~Jas

Re: [GENERAL] Script for reading flat file without delimiters

[Tino Wildenhain]

Mehdi Aboulkassim schrieb:

If I had to write it in java, I wouldn't mail this issue.
 
I somewhat a Neophyte in Postrgres that's why I need your help.
 
I've searched in the netbut I haven't found a script in Plpgsql that can 
read a file.

Can you tell me if we can do this in Plpgsql?


No you cant read in a file like that with psql. You cant
access filesystem with plpgsql either (quick check
in the docs would have tell you that...)

I was suggesting you write at least a converter program
in whatever language you are confortable with. May it
be java or whatever.

In that script you either access the database directly
(usual JDBC stuff) either via ordinary INSERT INTO ...
statements or if you feel desparete with COPY FROM STDIN
and then streaming.

IF thats too hard for you, just write a converter which reads
your file and outputs a regular CSV file. You can then use
psql command line tool to pull this into the database
(via its COPY function) or via backend with the COPY SQL
command.

If thats all too complex for you, I'm sorry you should stay
away from coding...

Regards
Tino Wildenhain

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

Re: [GENERAL] phppgadmin

[Robert Treat]

On Thursday 29 June 2006 08:59, Alain Roger wrote:
> Hi,
>
> I installed PHPpgadmin and there is a strange behavior.
> everytime that i select an object (database, table, schema,..) phppgadmin
> asks me to enter my login and password.
>
> why did it not keep this information from 1st connection ?
>

Normally this is due to an issue with your session support (ie. it can't 
retain information in the session when moving from one window to the next).  
Make sure you're allowing cookies in your browser from your phppgadmin host 
and/or make sure you have session support setup in your php. 

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

---(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] User privileges in web database applications

[John DeSoi]

On Jun 29, 2006, at 3:37 AM, Antonis Christofides wrote:


The benefit of this solution is that
I avoid reconnecting to the database on each request, having instead a
persistent connection as user "postgres".


But it is also a resource liability. How do you know if the user will  
make another request or when they are "finished" using your site? You  
can certainly time out the connection but for a busy site you will  
potentially be holding open a large number of connections that will  
never be used again.


I agree with your reasons for wanting to do this, but it may not be  
feasible for a busy web site.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for 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] pg_restore: [archiver] could not open input file

[Tom Lane]

[EMAIL PROTECTED] writes:
> C:\www\foo.com\trunk\db>pg_restore -h localhost -U postgres -W
> mySecretPassword script.sql

I don't think you're supposed to put the password on the command line
like that.  It's probably taking "mySecretPassword" as the file name
to read.

regards, tom lane

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

[GENERAL] Upgrade problem

[Nik]

I am trying to upgrade from 8.1.3 to 8.1.4 on Windows XP. I am a user
with admin rights and I am able to install programs, services and such.
I used the same account to install Postgres in the first place.
However, when I try to run the upgrade batch file, it goes through most
of the installation (file copying and such) and at the part where it is
supposed to install the Postgres service it gives me the following
error:

"Service 'PostgreSQL Database Server 8.1' (pgsql-8.1) could not be
installed. Verify that you have sufficient privileges to install system
services."

I logged in as Administrator and tried it again, and I got the same
error. What could be the issue?

Thanks.


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

[GENERAL] pg_restore: [archiver] could not open input file

[lbolognini]

Hi all,

I'm not able to restore a pg 8.1 database. The dump has been generated
with pg_dump. I tried with both absolute and relative path but it
doesnt find the file. Any clue?

C:\www\foo.com\trunk\db>pg_restore -h localhost -U postgres -W
mySecretPassword script.sql

pg_restore: [archiver] could not open input file: No such file or
directory

Thanks,
Lorenzo


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

Re: [GENERAL] Script for reading flat file without delimiters

[Michael Fuhr]

On Thu, Jun 29, 2006 at 03:10:03PM +0200, Mehdi Aboulkassim wrote:
>   I have to write a function that takes a flat file name in parameter
> and load a table with the file information.

Does it matter if this function runs on the server side or in the
client?

>   I think that it has to read the file line per line and parse each
> one to retreive each column data.
>
>   There is NO DELIMITERS between fields.

How are field boundaries recognized?  By column position?  By
patterns in the content?  This might be easiest with a language
like Perl, Python, Tcl, etc., whether on the client side or in a
server-side function written in one of those languages (PL/Perl,
PL/Python, PL/Tcl, etc.).  You could do it with SQL but other
languages are more suitable for text parsing.

-- 
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] Script for reading flat file without delimiters

[Tino Wildenhain]

Mehdi Aboulkassim schrieb:

Hy everybody,
 
I have to write a function that takes a flat file name in parameter and 
load a table with the file information.


Which programming languages do you plan to use?

I think that it has to read the file line per line and parse each one to 
retreive each column data.


probably.


There is NO DELIMITERS between fields.
 

How can the fields identified then? Fixed size?
You need to provide much more details - otoh,
it should not be too hard with a scripting
language and just some script you write.

Regards
Tino

---(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] Script for reading flat file without delimiters

[Mehdi Aboulkassim]

Hy everybody,     I have to write a function that takes a flat file name in parameter and load a table with the file information.     I think that it has to read the file line per line and parse each one to retreive each column data.     There is NO DELIMITERS between fields.     Can someone help.     Thanks in advance. 
		 Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs exceptionnels pour appeler la France et l'international.
Téléchargez la version beta.

Re: [GENERAL] PHPpgadmin and user privileges

[Nikolay Samokhvalov]

On 6/29/06, Alain Roger <[EMAIL PROTECTED]> wrote:

Hi,

I would like to provide access to PostgreSQL via PHPpgadmin.

basically when user will enter login + password, he will connect directly to
his database.
what i've done till now, it does not work.

 when user enter login and password, phppgadmin returns "Login failed".
my user hasfor profile "kmt_admin" which belong to "kmt_ad" group role" and
which is owner of "kmt" database.

Could you tell where to check to avoid such error ?


check your pg_hba.conf
http://www.postgresql.org/docs/7.3/static/client-authentication.html#AUTH-PG-HBA-CONF

most probably that you've forgotten to allow connections from that
host where phppgadmin is installed


--
Best regards,
Nikolay

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

  http://archives.postgresql.org

[GENERAL] phppgadmin

[Alain Roger]

Hi,I installed PHPpgadmin and there is a strange behavior.everytime that i select an object (database, table, schema,..) phppgadmin asks me to enter my login and password.why did it not keep this information from 1st connection ?
thanks a lot,Alain

[GENERAL] PHPpgadmin and user privileges

[Alain Roger]

Hi,I would like to provide access to PostgreSQL via PHPpgadmin.basically when user will enter login + password, he will connect directly to his database.what i've done till now, it does not work.
when user enter login and password, phppgadmin returns "Login failed".my user hasfor profile "kmt_admin" which belong to "kmt_ad" group role" and which is owner of "kmt" database.
Could you tell where to check to avoid such error ?thanks a lot,Alain

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Simon Riggs]

On Thu, 2006-06-29 at 14:27 +0200, Martijn van Oosterhout wrote:
> On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> > The issue is the difference between start of transaction and time when
> > the serializable snapshot is taken. Since BEGIN and other commands may
> > be issued as separate network requests it makes sense to defer taking
> > the snapshot until the first time it is needed. The transaction is still
> > serializable, just that the manual is worded slightly incorrectly with
> > regards the exact timing.
> 
> I've always interpreted it as "there exists a serialised order for the
> transactions" but the database makes no guarentees about what it might
> be. I can't think of any real world case where you actually care about
> the order, just as long as one exists.

Agreed, the ordering is irrelevant. 

Deferring the snapshot provides you with the most accurate *and*
consistent view of the database.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


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

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

Re: [GENERAL] User privileges in web database applications

[Alvaro Herrera]

Antonis Christofides wrote:

> But I think that checking user privileges at the database level is
> better.  I think it's simpler and more secure, and if later you also
> want to create nonweb apps, you won't have any more
> authentication/privilege headaches.  For this reason, in a web app
> I've made, the app connects to the database as user postgres, and
> after authenticating (receives user's password, checks with pg_shadow,
> and uses session cookie) uses "set session authorization" in order to
> lower its privileges.

What stops the user code from issuing a "RESET SESSION AUTHORIZATION"
command, say from a SQL injection, thus regaining superuser privileges?

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

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

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Martijn van Oosterhout]

On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> The issue is the difference between start of transaction and time when
> the serializable snapshot is taken. Since BEGIN and other commands may
> be issued as separate network requests it makes sense to defer taking
> the snapshot until the first time it is needed. The transaction is still
> serializable, just that the manual is worded slightly incorrectly with
> regards the exact timing.

I've always interpreted it as "there exists a serialised order for the
transactions" but the database makes no guarentees about what it might
be. I can't think of any real world case where you actually care about
the order, just as long as one exists.

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


signature.asc
Description: Digital signature

Re: [GENERAL] Strange Behavior with Serializable Transcations

[Simon Riggs]

On Wed, 2006-06-28 at 21:20 +0200, Martijn van Oosterhout wrote:
> On Wed, Jun 28, 2006 at 02:48:01PM -0400, Brad Nicholson wrote:
> > I'm seeing something fairly unintuitive about serializable transactions.
> > 
> > Taking the following test case:
> 
> 
> 
> > http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html
> > 
> > "When a transaction is on the serializable level, a SELECT query sees
> > only data committed before the transaction began; it never sees either
> > uncommitted data or changes committed during transaction execution by
> > concurrent transactions."
> 
> I think the issue here is that transaction begin is not when you type
> "begin" but at your first actual query. 

The issue is the difference between start of transaction and time when
the serializable snapshot is taken. Since BEGIN and other commands may
be issued as separate network requests it makes sense to defer taking
the snapshot until the first time it is needed. The transaction is still
serializable, just that the manual is worded slightly incorrectly with
regards the exact timing.

> You can obviously only start a
> transaction once you know what serialisation level you want, and you
> don't see that till after the begin.

Isolation level can be set on the BEGIN statement, so sometimes you do
know, but as mentioned above that's no reason to take the snapshot at
that point.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com


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

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

Re: [GENERAL] empty text fields

[Alban Hertroys]

Leif B. Kristensen wrote:

 0 = before (date1),
 1 = say (date1),
 2 = ca. (date1),
 3 = exact (date1),
 4 = after (date1),
 5 = between (date1) and (date2),
 6 = (date1) or (date2),
 7 = from (date1) to (date2).


This kind of reeks like a begin/end date and an accuracy quantifier, 
though that wouldn't account for option 6.


Your cases 0 to 5 and 7 would be transformed into something like:

   date Adate B accuracy
0: NULL  date1  5
1: date1 date1  3
2: date1 date1  2   (is 'ca.' less accurate than 'say'?)
3: date1 date1  5
4: date1 NULL   5
5: date1 date2  2
7: date1 date2  5

Where I defined '5' as being accurate, and lower values less accurate. 
You may want to use values with a wider spread, it'll allow more 
fuzziness about how sure you are about a certain date.


Just my 2 centims.

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

  http://archives.postgresql.org

[GENERAL] User privileges in web database applications

[Antonis Christofides]

Hi,

Most web database applications I've seen use a system separate from
the rdbms's user database for managing user privileges.  For example,
there may be a "users" table, or there may be external authentication
with, say, LDAP.  Or, for example, in MoinMoin (an extensible wiki,
where apps can be written as MoinMoin plugins), you can use MoinMoin's
authentication system.  The application does the authentication, and
it also implements permissions checking.

But I think that checking user privileges at the database level is
better.  I think it's simpler and more secure, and if later you also
want to create nonweb apps, you won't have any more
authentication/privilege headaches.  For this reason, in a web app
I've made, the app connects to the database as user postgres, and
after authenticating (receives user's password, checks with pg_shadow,
and uses session cookie) uses "set session authorization" in order to
lower its privileges.  I've even written triggers to implement
row-level permissions checking.  The benefit of this solution is that
I avoid reconnecting to the database on each request, having instead a
persistent connection as user "postgres".  One disadvantage, however,
is that, since I use identd to allow the web server user (www-data) to
connect as postgres, a web server compromise shall mean a compromise
of the database as user postgres.

I'm considering developing applications with MoinMoin, and I'd like to
have a unified user database used both by the wiki and by the
database.  I can probably hack MoinMoin to use postgresql for
authenticating.  But how may I have persistent database connections
without the security risk I described?  Another issue is that I may
have thousands of users, as is common in open web-accessible
databases; could this be a problem for PostgreSQL?  I'd also like your
general opinion or pointer on the issue of authentication and
privilege checking of web db apps; all I can find on Google is
tutorials that tell you how to create a "users" table and do all
checking at the application level.

Thanks!

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

   http://archives.postgresql.org