Re: Authentication in batch processing

Password prompting was changed in 7.2.X.  You can now pass a script into
psql, and you will be prompted for the password on your terminal rather
than having the password coming from the script.

The best way send the password in 7.2.X is to use 'expect', or use the
PGPASSWORD environment variable. (However, on some OS's, environment
values like PGPASSWORD can be seen by 'ps'.)  Another option is that if
/dev/tty can't get opened, the password will be requested from stdin.
Unfortunately, I can't think of an easy way to make /dev/tty fail.

---------------------------------------------------------------------------

Hal Lynch wrote:
> I have postgres set up to require usernames and passwords for access.
> 
> The following script works just fine in 7.1.2 without manual
> intervention.  Using 7.2.1 psql issues a password prompt and
> blocks until a password is manually entered.
> 
> #######################################
> #!/bin/sh
> 
> psql mydb <<\__EOD__
> mypassword
> create table mytable
> 	(
> 	ssn		char(9),
> 	name	char(50)
> 	);

Re: configure datatype name > 31?

Tom Lane wrote:
> "Mark McEahern" <marklists <at> mceahern.com> writes:
> > Tom, thanks for your reply.  I am now able to create databases and users
> > with longer names, but I am unable to login with the long username.
> 
> I think you're stuck on that, unless you want to be *really*
> incompatible with the rest of the world.  The startup packet format has
> hard-wired field sizes --- see src/include/libpq/pqcomm.h:
> 
> #define SM_DATABASE		64
> #define SM_USER			32
> #define SM_OPTIONS		64
> #define SM_UNUSED		64
> #define SM_TTY			64
> 
> Don't ask me why SM_USER is different from the rest 
> 
> If you change these I'd strongly advise bumping the protocol minor
> version number, so that you don't have weird behavior should you try
> to interoperate with standard code.
> 
> This is another thing that should be on the list of stuff to fix when
> we next change the FE/BE protocol ...

Comment added to source that SM_USER length should match the others.

--

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman <at> candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue

Re: Mirroring ENTIRE DATABASE.

Dear Rajesh Kumar Mallah,

Thankx for your response and sorry for my late response. Here find the
link for pgreplicator user discussion.

http://sourceforge.net/forum/forum.php?forum_id=60248

I wasn't aware of usogres & rserv forums and would be better if
someone point those links.

regards,
bhuvaneswaran.

On May 30, Rajesh Kumar Mallah. wrote:

> Dear Bhuvan,
> 
> Advanceded replication features although important
> are not currently available with postgresql.
> 
> 
> Some solutions do exists like pgreplicator, usogres
> rserv etc, but not sure of their status.
> 
> Also i would like to ask the list if there exists
> some place where these are discussed,..
> 
> 
> regds
> Mallah.

drop table failed to drop associated sequence

Re: drop table failed to drop associated sequence

On Mon, Jun 03, 2002 at 12:59:27PM -0300, Juan Rojas wrote:
> Hi,
> I'm running PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96.
> I have a problem when I drop a table which contains a serial field. The
> system drops the table but the associated sequence still remains in the
> catalog, so if I want to create the table again with the same structure,
> CREATE TABLE throws an error because the sequence that it's trying to create
> already exists.

That's easy enough: just run a 'DROP SEQUENCE <sequence_name>' to remove the
sequence before re-creating the table. You may discover the name of the
sequence using '\ds' in psql.

--

-- 
Henry House
The attached file is a digital signature. See <http://romana.hajhouse.org/pgp>
for information.  My OpenPGP key: <http://romana.hajhouse.org/hajhouse.asc>.

performance issues with DBI module when data too big

Re: performance issues with DBI module when data too big

Are you using the {AutoCommit => 0} argument to DBI->connect()?  If not,
do so, and then add a $dbh->commit; line when you're done with the
inserts. Should help a lot.

----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists <at> perrin.socsci.unc.edu * andrew_perrin (at) unc.edu

On Mon, 3 Jun 2002, Nicolas Nolst wrote:

> 
> Hi all,
> 
> I have developped a perl script to populate a database with two tables:
> sessions and actions.
> 
> the table actions contains the following columns: session_id, url,
> timestamp. The column session_id references to the table sessions. 
> 
> the table sessions contains the following columns: session_id, remote_ip,
> phone_type, phone_number. The column session_id is serial.
> 
> The lines of the table actions which are part of the same session have
> the same session_id.
> 
> There are then more lines in the table actions than in the table session.
> 
> 
> To fill the two tables, I first need to know if the session already
> exists for a certain phone_type, a certain remote_ip and a certain
> phone_number:
> 
> SELECT session_id FROM sessions WHERE (phone_number = ?) AND (remote_ip 
> = ?) AND (phone_type = ?) ORDER BY session_id;
> 
> I also need to apply a criteria to know if I have to add a new entry in
> the table sessions or not:
> 
> SELECT (max(timestamp) + ?)<? FROM actions WHERE (session_id = ?);  
> 
>  
> 
> If the session already exists I add a line in the table actions with a
> INSERT
> 
> If the session doesn't exist or if the criteria is true, I add a line in
> the table sessions with an INSERT, retrieve the session_id of the line
> just added with the following request
> 
> SELECT session_id FROM sessions where (msisdn=?) AND (remote_ip=?) AND
> (user_agent=?) ORDER BY session_id DESC LIMIT 1
> 
> and the add with a INSERT a line in the table actions.
> 
> I have put indexes on sessions(session_id), sessions(msisdn),
> actions(session_id).
> 
> My problem is that populating my database is slower  when the data gets
> bigger and the performance falls dramatically. I thought that is would be
> improve with my indexes but the problem still persists.
> 
> Could you please give me some clues that could solve this issue.
> 
> Thanks.
> 
> 
>  
> 
> Nicolas Nolst
> 
> [belgium_gs.gif]
> 
> ________________________________________________________________________________
> Join the world’s largest e-mail service with MSN Hotmail. Click Here
> 
> 

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org

multiple instances on one box?

Hi,

Hopefully not a FAQ, but I wonder, how do I start multiple Postgres
instances on one computer?

To elaborate, I want to start one Postgres listening on say 7000 for
$DATA=/opt/pgdata1 and another Postgres listening on say 7100 for
$DATA=/opt/pgdata2.

Thanks,
Tim Ellis
DBA, Gamet

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly

Re: multiple instances on one box?

What's the reason for doing this? Just use separate databases -- if
necessary, with different users given permissions on each -- in the same
postmaster instance.

ap

----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists <at> perrin.socsci.unc.edu * andrew_perrin (at) unc.edu

On Mon, 3 Jun 2002, Tim Ellis wrote:

> Hi,
> 
> Hopefully not a FAQ, but I wonder, how do I start multiple Postgres
> instances on one computer?
> 
> To elaborate, I want to start one Postgres listening on say 7000 for
> $DATA=/opt/pgdata1 and another Postgres listening on say 7100 for
> $DATA=/opt/pgdata2.
> 
> Thanks,
> Tim Ellis
> DBA, Gamet
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo <at> postgresql.org so that your
> message can get through to the mailing list cleanly
> 

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly

Re: multiple instances on one box?

At 12:18 PM 6/3/2002 , Andrew Perrin wrote:
>What's the reason for doing this? Just use separate databases -- if
>necessary, with different users given permissions on each -- in the same
>postmaster instance.

We will be using separate instances for failover purposes in our clustered 
environment.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly