Re: [GENERAL] How feasible is this?

[Chris Smith]

On Fri, 2010-05-21 at 16:08 +0800, Craig Ringer wrote:
> My non-expert feeling is that you could possibly extend a predicate 
> locking scheme to do this. It's something that'd maybe be possible by 
> hooking into the predicate locking schemes being being designed to 
> support true serializability in Pg (see periodic discussion on -hackers) 
> but those locking schemes aren't in the main PG code yet.

Thanks for that.  Gives me a place to start looking!

> It might be a good idea to take a few steps back and look at what you 
> are trying to achieve with this. Why do you want it? What for? What 
> problem will it solve for you?

It's not a typical application-level problem.  I'm playing with the idea
of extending the transactional memory system in the Haskell programming
language so that database access can be done inside of an application
atomic block.  Currently, database access counts as I/O, and therefore
must be done outside of atomic blocks, and this leads to a somewhat
strained programming model for applications combining transactional
memory with databases.

Haskell's transactional memory provides exactly the feature I'm asking
for: specifically, there is a "retry" action, which rolls back a
transaction, blocks until there's some change that makes it likely that
the transaction will behave differently in the future, and then retries
it.  This turns out to be very useful for transactional memory.  Whether
it's useful for database access or not may be an open question, but it
seems very messy to say "don't retry if you've touched the database",
since the point here is to be composable and not make people worry about
the implementation details of some other part of their transactions.

-- 
Chris Smith



-- 
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 feasible is this?

[Chris Smith]

I'm writing in desperate hope that something like this exists... because
if so, it would make my life a lot easier.  I want to be able to:

a) Roll back a transaction

b) Receive a notification when retrying the exact same transaction might
cause different data to be returned from something that was done up to
the point of the rollback; i.e., some result set, update count, etc.
might be different.

It's okay if (b) is overly sensitive.  For example, one technically
correct (but disappointing) answer would be to get that notification
immediately on the rollback; but that would cause the application to
spin and retry the same transaction in a loop with no delays, which is
hardly ideal to say the least.

Any ideas on doing better than that?

-- 
Chris Smith



-- 
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] Bigtime scaling of Postgresql (cluster and stuff I suppose)

[chris smith]

> Ever read anything on how myspace is laid out?  The big ones need
> replication to handle the traffic.

Actually no.

http://highscalability.com/livejournal-architecture

"Using MySQL replication only takes you so far." (Yeh it's mysql but
the point is valid regardless).
"You can't keep adding read slaves and scale."

A lot use sharding now to keep scaling (limiting to "X" users/accounts
per database system and just keep adding more database servers for the
next "X" accounts).


Myspace info here:

http://highscalability.com/myspace-architecture

At 3mill users:

- split its user base into chunks of 1 million accounts and put all
the data keyed to those accounts in a separate instance of SQL Server

I'm sure there's replication behind the scenes to help with
read-queries but it's definitely not a magic wand that will fix
everything.

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

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

Re: [GENERAL] jdbc pg_hba.conf error

[chris smith]

On 5/31/07, Bhavana.Rakesh <[EMAIL PROTECTED]> wrote:


 Ok,
 I confirmed that I'm editing the right pg_hba.conf file.  I made sure that
there are no other postmasters running.  I made sure that there is a user
called 'brakesh'.  I restart the postmaster everytime I make any changes to
pg_hba.conf file.  But still same results!

 [EMAIL PROTECTED] ~/db_connect]$ psql -U brakesh -h 127.0.0.1 -d testing123
 psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off


 [EMAIL PROTECTED] ~/db_connect]$ psql -p 5000 testing123
 Welcome to psql 7.4.17, the PostgreSQL interactive terminal.


Why are you specifying the port number when you don't include the host?

What happens if you do include the port:

psql -U brakesh -p 5000 -h 127.0.0.1 -d testing123

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

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

Re: [GENERAL] what is the default username password for PostgreSQL, which was installed with FC5.

[chris smith]

On 10/27/06, Purusothaman A <[EMAIL PROTECTED]> wrote:

Thanks for all of your valuable replies.

Please tell me, what should i do to login with syntax

  psql  -Uusername  -h192.168.2.2  -dusername(database name).

Because I should be able to login from any login and also from any  system.


Set up your pg_hba.conf file to what you need.

I have a small article about this on my site:

http://www.designmagick.com/article/4/

The postgres documentation is here:
http://www.postgresql.org/docs/current/static/client-authentication.html
--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] Best approach for a "gap-less" sequence

[chris smith]

On 8/12/06, Jorge Godoy <[EMAIL PROTECTED]> wrote:


Hi!


I was trying to solve a problem on an old system and realized that there might
be some better approach for doing what I need.

We have some documents that need to be ordered sequentially and without gaps.
I could use a sequence, but if the transaction fails then when I rollback the
sequence will already have been incremented.

So, today I have a control table and I acquire a SHARE ROW EXCLUSIVE lock to
it, read the value, increase it, do what I need and then I COMMIT the
transaction, ensuring that the sequence has no gaps.

Is there a better way to guarantee that there will be no gaps in my sequence
if something goes wrong with my transaction?


Why does it matter?

I assume there is a reason you need it like this..

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

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

Re: [GENERAL] pgsql user change to postgres

[chris smith]

On 7/2/06, Joe <[EMAIL PROTECTED]> wrote:

Hi,

I started using PostgreSQL (8.0) about a year ago on Windows.  Following
the installation instructions, I created a 'postgres' user (BTW, this
was based on the Short Version instructions for UNIX, i.e., "adduser
postgres" --I'm not sure it this is made explicit elsewhere, even for UNIX).

I'm now migrating to FreeBSD and was surprised to find that the port
used 'pgsql' as the user.  The maintainer said that was done to ensure
backward compatibility because that *was* the original name.  Since I
didn't need to be backward compatible (and my Windows dbs already used
'postgres'), I tried to bypass that (sort of) requirement by renaming
'pgsql' to 'postgres' (in the passwd file) and changing the
postgresql_user variable used in the rc startup file.  That was OK until
I tried to build 8.1.4_1.  I figured out how to tweak the build files to
stick with 'postgres' but then I realized I'd have to patch them every
time I'd fetch a new build, so I went back to 'pgsql'.

I'm curious about a few things.  How long ago was the 'pgsql' to
'postgres' change (and maybe it would be helpful to know the rationale
for the backward incompatible decision--I tried searching in the
archives but 'pgsql' and 'postgres' are all too common)?  Is there any
problem with using 'pgsql' vs. 'postgres' (and are there any plans to
deprecate or disallow the former at some point)?  Are other UNIX/Linux
ports in the same boat, or does any Linux port offer users a choice in
this matter?  Would any change to the build/install procs have to be
done through the current port maintainer or are they somewhere in the
PostgreSQL source tree (and subject to standard submission/review
procedures)?


I *think* that's a bsd decision to change the name. All of the linux
systems I have used for the last 5-6 years have used 'postgres' as the
user.

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

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

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

Re: [GENERAL] Installation problems

[chris smith]

On 6/30/06, Victor Escobar <[EMAIL PROTECTED]> wrote:

Hello,
  I'm going through the elongated instructions of installing pgsql and
am stuck at the point where one types:

%> su - postgres

When I type this and type in the password I chose, I get the following
error: 'su: no directory'


What directory does /etc/passwd have for the postgres user? Does it exist?

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

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

Re: [GENERAL] Computing transitive closure of a table

[Chris Smith]

Thanks for everyone's suggestions.  I found the following, which at least 
seems to meet my needs temporarily.


   http://citeseer.ist.psu.edu/dong99maintaining.html

Should it turn out that this is not feasible to implement via triggers in 
PostgreSQL, I may be back with more questions and seek out a route that 
involves modifying the database or other such things.


--
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation 



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

Re: [GENERAL] Computing transitive closure of a table

[Chris Smith]

Oleg Bartunov wrote:

Chris,

have you seen contrib/ltree ?


I hadn't.  Thanks!  I will look into it further, but I'm currently a bit 
concerned by the word "tree" in the title.  Many of the problems I'm solving 
are not trees, though nearly all are DAGs.


--
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation 



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

[GENERAL] Computing transitive closure of a table

[Chris Smith]

I am doing some preliminary work on the next major release of a piece of 
software that uses PostgreSQL.  As odd as this sounds, it seems that a huge 
percentage of the new features that have been requested involve computing 
the transitive closure of a binary relation that's expressed in a database 
table.


For example:

- Given a list of relationships of the form "X is a direct subgroup of Y", 
determine the full list of groups of which some group is a (not necessarily 
direct) subgroup.


- Given a list of statements of the form "X must happen before Y", determine 
everything that needs to happen for some objective to be achieved.


And the list goes on and on...  I'm aware that it's not possible to solve 
the transitive closure problem using a simple SQL query.  Anyone have any 
recommendations?  Are there any thoughts on implementing efficient 
transitive closures within PostgreSQL?  If I wanted to do it, are there 
preferences on syntax or other such things?


My thoughts on an ideal feature would involve being able to create a sort of 
"transitive closure" index which could be kept up to date automatically by 
the database back end.


Or should I just punt and let the queries be slow (not a good option, since 
the group thing is necessary for permission checking, which may happen up to 
a half-dozen times per HTTP request).


Thanks,

--
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation 



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

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

Re: [GENERAL] ean code data type

[chris smith]

On 6/7/06, Ottavio Campana <[EMAIL PROTECTED]> wrote:

Is there a data type for ean codes for postgresql 7.4? I found the isbn
data type, and I would appreciate something similar for ean codes.


If there isn't you can create your own:

http://www.postgresql.org/docs/8.1/static/sql-createtype.html

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

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

Re: [GENERAL] postgres in windows

[chris smith]

On 6/1/06, Antonios Katsikadamos <[EMAIL PROTECTED]> wrote:


Hi all. My name is Antonios and I am doing an MSc in Advanced computing at
Imperial College London.

I need to install postgres for my individual project on windows.

Would it be a problem to ask one- or -two questions?

First of all is there an installer for postgres for windows?


http://www.postgresql.org/ftp/binary/v8.1.4/win32/

Get the postgresql-8.1.4-1.zip file.

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

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

Re: [GENERAL] allow LIMIT in UPDATE and DELETE

[chris smith]

On 5/19/06, Csaba Nagy <[EMAIL PROTECTED]> wrote:

Hi all,

Currently the LIMIT clause is not allowed in UPDATE or DELETE
statements. I wonder how easy it would be to allow it, and what people
think about it ? For our application it would help a lot when processing
things chunk-wise to avoid long running queries.


I asked that question a while ago..

http://archives.postgresql.org/pgsql-general/2005-04/msg00386.php

and got this response:

http://archives.postgresql.org/pgsql-general/2005-04/msg00387.php

Works quite well :)
--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] Creating of User and Database

[chris smith]

On 5/11/06, APSC, Patrick Chee Seng Onn <[EMAIL PROTECTED]> wrote:

Hi,

  I've just installed postgresql onto my system but unable to successfully add 
new users and databases. I would receive a error message:

createuser: could not connect to database template1: FATAL user "root" 
does not exist

createdb: could not connect to database template1: FATAL user "root" 
does not exist

  what is the error I am facing anyway?


Either try:

createuser -U postgres new_username

or, su to postgres:

su - postgres

and try again:

createuser new_username


http://www.designmagick.com/article/5/Starting-Out/PostgreSQL-Users
http://www.designmagick.com/article/6/Starting-Out/PostgreSQL-Databases

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

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

  http://archives.postgresql.org

Re: [GENERAL] Connecting to PostgreSQL on Linux with windows

[chris smith]

On 10 May 2006 07:46:01 -0700, mmaclennan <[EMAIL PROTECTED]> wrote:

Hi, I have a linux box (Fedora 3) running the latest version of
PostgreSQL and PostGIS. I am trying connect to the database through a
windows computer but can't seem to make the connection work. I've
configured the pb_hba.config file in the usr directory to as "host
all all 192.168.1.0/24md5" but it doesn't seem
to work.

I know these are the correct settings because I installed PostgreSQL on
a windows based machine and can access that version no problem with the
aformentioned settings. Can a windows machine connect to a Linux
version of Postgresql if so what am I doing wrong?


What exactly "doesn't work" ? Do you get an error message? Does it time out? ...

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

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

Re: [GENERAL] Debugging SQL queries

[chris smith]

On 5/11/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote:

From my point of view, more important problem is that log doesn't help
to find the query (in other words, log message doesn't show context)


Yes it does.. but it depends on your logging setup.

in psql:

test=# blah;
ERROR:  syntax error at or near "blah" at character 1

my log shows:

... [9-1] LOG:  statement: blah;
... [10-1] ERROR:  syntax error at or near "blah" at character 1

Do you have:

log_statement = true

in your postgresql.conf ?

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

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

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

Re: [GENERAL] understanding explain data

[chris smith]

On 5/10/06, Alban Hertroys <[EMAIL PROTECTED]> wrote:

Sim Zacks wrote:
> Something such as: with this explain data, adding an index on table tbl
> column A would drastically improve the efficiency. Or at least an
> application that would say, the least efficient part of your query is on
> this part of the code so that you could more easily figure out what to
> do about it.

The latter part is the most useful IMO, optimizing usually needs a
(human) brain to put things into the right perspective. Adding an index
can speed up your queries only so much, a more optimal data presentation
  (like moving calculations to insert/update instead of select) can do a
lot more sometimes.

It looks like something like that shouldn't be too hard to write...
Maybe it even does exist already. Personally I'd prefer a command line
tool ;)
It would help if you can pipe the output of explain analyze to an
external tool from within psql.


I've thought about writing a similar tool.. I'm about 30% of the way :)

It's written in python and can grab the queries out of the db logs..
but the harder part is working out the explain output.. also taking in
to consideration an index might be available but not the best option
for the query.

I guess the easiest way to check is to have the script turn seq scans
off when it runs explain and go from there.

If anyone's interested in helping it go further contact me off list
(can put it on pgfoundry.org and go from there if need be).

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

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

  http://archives.postgresql.org

Re: [GENERAL] Catch individual exceptions

[chris smith]

On 3 May 2006 19:16:17 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

hi all,
i want to capture individual sqlexceptions and for each different
exception i want to display a different message to the user. the
problem is how should i capture from the exception of its type-content
and then display a customised message.


Wouldn't the programming language you're using handle this? (Which
language is it?)

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

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

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

Re: [GENERAL] simple md5 authentication problems

[chris smith]

On 5 May 2006 02:22:32 -0700, robert <[EMAIL PROTECTED]> wrote:

Hi all, hope this is the right list.

I have postgres 8.1 running on linux. We have tests that mostly run on
windows. I want to run these tests on linux.

On these windows boxes, pg_hba.conf has just one line:

hostall all 127.0.0.1/32  md5

They use 'postgres' as the user and password to connect to a db.

I couldn't start postgres on linux with just that line, so on linux I
have:

# "local" is for Unix domain socket connections only
local   all all   ident sameuser
# IPv4 local connections:
hostall all 127.0.0.1/32  md5
# IPv6 local connections:
hostall all ::1/128   ident sameuser

I created my db as:
postgres=# CREATE DATABASE maragato_test OWNER postgres;

I seem to have a user 'postgres' - I'm using the default.

postgres=# SELECT * FROM "pg_user";
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil | useconfig
--+--+-+--+---+--+--+---
 postgres |   10 | t   | t| t |  |
|

However, I get this error:

/home/postgres> psql -h localhost maragato_test postgres
Password for user postgres:
psql: FATAL:  autenticação do tipo password falhou para usuário
"postgres"

Sorry - couldn't get local en_US working. That translates to:
Authentication of type password failed for user postgres. I think that
means 'ident password' . I tried to connect with java and I get the
same error.

I just need to connect to db 'maragato_test' on local host using
'postgres´ as the user and password, using md5.


Try '-h 127.0.0.1' rather than 'localhost' - it's still seeing the
connection as coming through the socket, not through tcpip, so it's
matching the "ident" rule.

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

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

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

Re: [GENERAL] Authentication & connection problems

[chris smith]

On 5/1/06, chris smith <[EMAIL PROTECTED]> wrote:

>  Here's the deal. For example, when trying to connect to an existing
> databas, or even
>  creating a new one with the command createdb [dbname],
>  different error occurs.

What errors exactly? We can't guess..

>  Another example: when running the command psql,
>  entering the password and hitting enter,
>  the following error message occur:
>
>  psql: FATAL:  password authentication failed for user "Christo"

Unless you created the user as "Christo" you should use christo -
postgresql is case insensitive unless you put it in quotes.


badly worded response there.

unless you put quotes around it, postgres turns it lowercase.

so Christo becomes christo ...

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

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

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

Re: [GENERAL] Authentication & connection problems

[chris smith]

 Here's the deal. For example, when trying to connect to an existing
databas, or even
 creating a new one with the command createdb [dbname],
 different error occurs.


What errors exactly? We can't guess..


 Another example: when running the command psql,
 entering the password and hitting enter,
 the following error message occur:

 psql: FATAL:  password authentication failed for user "Christo"


Unless you created the user as "Christo" you should use christo -
postgresql is case insensitive unless you put it in quotes.


 Similarly, when testing the TCP/IP connection with the
 command psql -U DATABASENAME -W -h localhost the following message shows up
 psql: FATAL:  database "dspace" does not exist


-U is for username, not database name.

Since you're not specifying a database name, it will try to use the
same as the username (which you're passing in incorrectly).

So that's the same as:

psql -U dbname -W -h localhost dbname

which is probably not what you want.

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

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

Re: [GENERAL] Is PostgreSQL an easy choice for a large CMS?

[chris smith]

On 4/30/06, Tony Lausin <[EMAIL PROTECTED]> wrote:

Hello all,

I'm working on a CMS which requires an open source database capable of
handling hundreds of thousands of users simultaneously, with a high
rate of database writes, and without buckling. We're talking somewhere
between nerve.com/catch27.com and xanga.com/friendster.com

PostgreSQL is a personal favorite of mine, and my gut instinct is that
it's the best choice for a large scale CMS serving many users;
however, I'm getting antsy. I keep getting suggestions that Postgres
is really only suited to small and medium projects, and that I should
be looking at MySQL for a large scale database drive site. I'm not
really a fan of MySQL, but I'll consider it if it truly is the better
choice in this case. I just don't understand how it would be. I'm
thinking this is solely in reference to VACUUM. Even with autovacuum
suport, I tend to agree there is at least one handicap.

I could really use some enlightenment on just where PostgreSQL fits in
a single-server, highly-trafficked web site serving mostly text,
pictures and possibly streaming media.


http://people.planetpostgresql.org/xzilla/index.php?/archives/151-Sean-Chittenden-on-RubyOnRails-Podcast.html

http://www.postgresql.org/about/casestudies/

http://www.postgresql.org/about/users

are all good places to start.

TBH it depends a lot on your data and how you structure it. I wrote a
small tute on how to get rid of left-join type queries and use
triggers to keep count(*) type queries to a minimum..

http://www.designmagick.com/article/36/Forum-Project/Database-Design-Issues

It's not always possible, but there are ways to minimize count(*),
min(field), max(field) type queries where postgresql isn't able to
optimize fully due to mvcc issues.

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

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

Re: [GENERAL] Postgres on WinXP - app on Cygwin

[chris smith]

On 4/27/06, Tomas Lanczos <[EMAIL PROTECTED]> wrote:
> I am curious, whether is it possible to connect to a PostgreSQL database
> installed on WinXP for an application installed on the same box but running
> in the Cygwin environment (the app. is the GRASS GIS).

There are no reasons why this shouldn't work. Are you having a
particular issue with it?

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

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

Re: [GENERAL] PostgreSQL 8.1 + PHP5.1.x/4.4.2 + Apache 2.0.55/1.3.34 PROBLEM!! PLEASE HELP

[chris smith]

On 4/27/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Help! I was trying to make an installation of PHP 5.1.2 + Apache
> 2.0.55 + PostgreSQL 8.1 on Windows 2003 Server, and I'm stuck. I
> got PHP to work with Apache quite smoothly, so did I marry PHP with
> PostgreSQL - scripts connecting to the database work fine from
> windows command line, except that the following code:
>
> if (extension_loaded("php_pgsql")) {
> echo "PGSQL loaded!";
> }
>
> returns no message. Still database queries work fine. Trouble starts
> when I try to open a page in my browser - then I get an error
> message like this:
>
> Error: call to udefined function pg_connect()...
>
> I changed the php.ini file a billion times, trying to figure out
> what to set in the "extension_dir" and "extension=php_pgsql.dll"
> lines, and I tried at least as many times to change apache's
> httpd.conf file so that the php module is loaded properly. And it
> is, as far as I'm concerned - the phpinfo() page shows without a
> problem. One peculiar thing about it is that in the "Loaded
> modules" section (don't remember the exact name) there's absolutely
> no sign of the pgsql module.

you could change the first check to:

if (!function_exists('pg_connect')) {
  die("no pg_connect");
}

What does:

print_r(get_loaded_extensions());

show?

Anything regarding pgsql?

Does your server keep logs? Maybe something in there will give you
some ideas about what's going on..

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

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

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

Re: [GENERAL] Problem with complex outer join expression

[chris smith]

On 4/26/06, Chris Velevitch <[EMAIL PROTECTED]> wrote:
> I'm using 7.4.5 on win XP Pro SP1.
>
> I'm getting:-
>
>  ERROR:  syntax error at or near "(" at character 155
>
> from the query:-
>
> select dummy_records.sequence_nr,timesheets.weekending,timesheets.timesheet_id
> from dummy_records
>  ,left outer join timesheets
> on (timesheets.weekending = ('2006-04-09' + (integer
> dummy_records.sequence_nr-1)*7)))
> where dummy_records.sequence_nr between 1 and (date '2006-04-23' -
> date '2006-04-09')/7+1;
>
> What this query is trying to achieve is:-
>
> Find all weekending dates between 2 given weekending dates and any
> corresponding timesheets for those weekending dates.

Table joins can only be done against another table & field, I don't
think you can do it using an expression like this. That should all be
in the where clause.

What do the timesheets and dummy_records tables look like?

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

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

Re: [GENERAL] How to implement a "subordinate database"?

[chris smith]

On 4/19/06, Kynn Jones <[EMAIL PROTECTED]> wrote:
>
> I keep bumping against this situation: I have a main database A, and I want
> to implement a database B, that is distinct from A, but subordinate to it,
> meaning that it refers to data in A, but not vice versa.
>
> I don't simply want to add new tables to A to implement B, because this
> unnecessarily clutters A's schema with tables that entirely extraneous to
> it.

Hmm. Postgres supports table inheritance, but I don't think it
supports schema or database inheritance in the way you want it to.

http://www.postgresql.org/docs/8.1/interactive/tutorial-inheritance.html
http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html


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

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

Re: [GENERAL] hard shutdown of system

[chris smith]

On 4/17/06, surabhi.ahuja <[EMAIL PROTECTED]> wrote:
>
> the user tries to do kill -9 -1 and log in again
>
> in the startup script i do the following
>
> /sbin/pidof -s postmaster
>
> and it still displays some value,
>
> however ps -aef | grep postmaster does not display anything
>
> is it ok if i do the following
>  pid1=`/sbin/pidof -s postmaster`
>  pid2=`ps -eaf | grep postmaster | grep -v grep | tail -1 | awk '{print
> $2}'`
>
> if ($pid1 and $pid2)
> => postmaster is already running
>
> otherwise
>
> i check if postmaster.pid exists
> if it does, i delete it
> and then start postmaster by doing $PGCTL -l $POSTGRES_LOG -D $PGDATA -p
> $POSTMASTER -o '-p ${PGPORT}' start  > /dev/null 2>&1

Check out the startup script. Depending on what system you are
running, this might already all be taken care of.

Here's a mandrake example (I think the redhat version is pretty similar).

http://techdocs.postgresql.org/scripts/mandrake72-startup

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

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

Re: [GENERAL] Comparing text field

[chris smith]

On 13 Apr 2006 12:20:08 -0700, Nik <[EMAIL PROTECTED]> wrote:
> I am trying to compare a large string (that has new line characters in
> it) to the contents of the text field (which also has new line
> characters in it) and it is not behaving as expected.
>
> For example I have the following record in the database:
> id=83
> message=VAC153-683-685-131830-
> /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/
> BULLETIN - EAS ACTIVATION REQUESTED
> SEVERE THUNDERSTORM WARNING
> NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC
> 142 PM EDT THU APR 13 2006
> THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A
> * SEVERE THUNDERSTORM WARNING
>
> If I do the following query
> SELECT id FROM table1 WHERE message='VAC153-683-685-131830-
> /O.NEW.KLWX.SV.W.0022.060413T1742Z-060413T1830Z/
> BULLETIN - EAS ACTIVATION REQUESTED
> SEVERE THUNDERSTORM WARNING
> NATIONAL WEATHER SERVICE BALTIMORE MD/WASHINGTON DC
> 142 PM EDT THU APR 13 2006
> THE NATIONAL WEATHER SERVICE IN STERLING VIRGINIA HAS ISSUED A
> * SEVERE THUNDERSTORM WARNING'
>
> I get no results back, even though the message is equivalent. How
> should I perform this comparison so that the above query returns id=83?

Could one have \r\n and the other have \n ?

Are you doing the comparison in psql or through a language (php, ruby,
python, other) ?

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

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

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

Re: [GENERAL] Regex with patterns in table field

[chris smith]

On 4/14/06, pgdb <[EMAIL PROTECTED]> wrote:
>
>
> Hi Andreas,
>
> strange but I don't see html from my original email received from the
> mailing list, hope this reply is ok:)
>
> If I'm not wrong, the example you've provided is trying to return matching
> rows from multiple patterns and texts as inputs in the regex search.
>
> The text in regular expression have to be just a single string(with no white
> character) for my case. Matching should then be done with patterns from each
> row, returning the row(s) that contain matching regex pattern(s). Appreciate
> any advice. Thanks.

This page might be what you're after:

http://www.postgresql.org/docs/8.1/static/functions-matching.html

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

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

Re: [GENERAL] the integer type

[chris smith]

On 4/14/06, Zahir Lalani <[EMAIL PROTECTED]> wrote:
> Hi Chris
>
> I am using pgAdmin III.
>
> Created a table, then started adding fields. This is where the problem
> hits - the drop down does not give you an INTEGER option, so you cannot
> shoose it.
> Also I tried creating a script in the query view and running it, but
> again, if I used INT or INTEGER it errors. Change it INT4 and its fine.


What error do you get? I'm sure pgAdmin will show something.

Try it from console:

psql.exe dbname
create table t1(a int);

(Always CC the list, someone else might be able to help you if I don't
know the answer).

> > -Original Message-
> > From: chris smith [mailto:[EMAIL PROTECTED]
> > Sent: 13 April 2006 15:17
> > To: Zahir Lalani
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] the integer type
> >
> >
> > > I have only recently started to use Postgresql and have a
> > problem. I am
> > > using v8.1 on windows.
> > > I cannot seem to get the DB to accept either INT or INTEGER
> > as a type
> > > when using the admin tool. Only INT2 or INT4 work.
> > > I even downloaded Navicat trial and this has the same
> > issue. Is there a
> > > setup required to allow these
> > > standard types to be enabled?
> >
> > It's a built in type so you don't need to do anything.
> >
> > So something like this:
> >
> > create table t1(a int);
> >
> > fails?
> >
> > What message do you get?
> >
> > --
> > Postgresql & php tutorials
> > http://www.designmagick.com/
> >
>
>
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __
>


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

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

Re: [GENERAL] the integer type

[chris smith]

> I have only recently started to use Postgresql and have a problem. I am
> using v8.1 on windows.
> I cannot seem to get the DB to accept either INT or INTEGER as a type
> when using the admin tool. Only INT2 or INT4 work.
> I even downloaded Navicat trial and this has the same issue. Is there a
> setup required to allow these
> standard types to be enabled?

It's a built in type so you don't need to do anything.

So something like this:

create table t1(a int);

fails?

What message do you get?

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

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

   http://archives.postgresql.org

Re: [GENERAL] posting request

[chris smith]

On 4/13/06, Anton Andreev <[EMAIL PROTECTED]> wrote:
>
>   I want to post some questions.

Go ahead and post them :) We don't bite :)

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

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

Re: [GENERAL] pgcrypto-crypt

[chris smith]

On 4/6/06, AKHILESH GUPTA <[EMAIL PROTECTED]> wrote:
> dear all,
>  i want to encrypt and decrypt one of the fields in my table (i.e-password
> field)
>  i have searched and with the help of pgcrypto package, using function
> "crypt", i am able to encrypt my data,
>  but there is nothing which i found to decrypt that same data,
>  plz anybody give me the function to decrypt that encrypted value.

The crypt function can't be decrypted (whether it's in postgresql or
anywhere else).

Crypt is meant to be used for passwords and such that you don't need
to reverse (you only compare against).

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

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

Re: [GENERAL] what is this error

[chris smith]

On 4/3/06, venu gopal <[EMAIL PROTECTED]> wrote:
>
> Dear List,
> I have created a database called dhis13 and created a login role with
> user name venu and pwd gis now i have created an sql file using shp2pgsql
> command it worked successfully when i m importing the same to the dhis13
> database i was giving with the following error all the database and
> loginroles is created at pgadminIII (not at command prompt)
>When i try to import i was getting the following error
>
> C:\Program Files\PostgreSQL\8.1\bin>psql -U venu -d dhis13 -f
> e:\venu\postgischi
> ttoorshp.sql
> Password for user venu:
> BEGIN
> psql:e:/venu/postgischittoorshp.sql:2: NOTICE:  CREATE
> TABLE will create implici
> t sequence "chittoor_ver0_gid_seq" for serial column "chittoor_ver0.gid"
> psql:e:/venu/postgischittoorshp.sql:2: NOTICE:  CREATE
> TABLE / PRIMARY KEY will
> create implicit index "chittoor_ver0_pkey" for table "chittoor_ver0"
> CREATE TABLE
> psql:e:/venu/postgischittoorshp.sql:3: ERROR:  function
> addgeometrycolumn("unkno
> wn", "unknown", "unknown", "unknown", "unknown", integer) does not exist
> HINT:  No function matches the given name and argument types. You may need
> to add explicit type casts.

You're missing a function called "addgeometrycolumn". It's not a
native postgres function (as far as I'm aware) - you'll have to find
where it comes from and import it.

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

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

   http://archives.postgresql.org

Re: [GENERAL] encryption/decryption

[chris smith]

On 4/1/06, AKHILESH GUPTA <[EMAIL PROTECTED]> wrote:
> hi all,
>  just to ask u all one thing regarding encryption/decryption.
>
>  i am migrating my database from mysql to pgSQL using a php script.
>  in mysql there is a table 'users' where a field 'password' is there of type
> varchar(30),
>  and is encrypted using AES algorithm

The 'users' table in the 'mysql' database is md5'ed. You can't decrypt it.

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

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

   http://archives.postgresql.org

Re: [GENERAL] about partitioning

[chris smith]

On 4/1/06, fufay <[EMAIL PROTECTED]> wrote:
> dear all,
> i created a master table and a sub table which inherits the main table.
> and then i made a trigger and a function that want to keep the master table
> empty.
> but the trigger didn't work anyway.when i inserted data into the table
> "news",both the master table
> and the sub table were inserted.
> why? i just want the empty master table,any good ideas?
> lots of thanks for all.
>
> here r DDls:
> -
> --master table；
> CREATE TABLE "public"."news" (
> "id" SERIAL,
> "title" VARCHAR(100) NOT NULL,
> "content" VARCHAR NOT NULL,
> "author" VARCHAR(50) NOT NULL,
> "date"   DATE DEFAULT now(),
> CONSTRAINT "news_pkey" PRIMARY KEY("id")
> )WITHOUT OIDS;
>
> --rule；
> CREATE RULE "news_current_partition" AS ON INSERT TO "public"."news"
> DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES (new.title,
> new.content, new.author));
>
> --trigger；
> CREATE TRIGGER "news_triggers" BEFORE INSERT
> ON "public"."news" FOR EACH ROW
> EXECUTE PROCEDURE "public"."deny_insert"();
>
> --function；
> CREATE OR REPLACE FUNCTION "public"."deny_insert" () RETURNS trigger AS
> $body$
> BEGIN
> RETURN NULL;
> END;
> $body$
> LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
>
> --sub table；
> CREATE TABLE "public"."news_001" (
> CONSTRAINT "news_001_date_check" CHECK ((date >= '2006-03-29'::date) AND
> (date < '2006-04-28'::date))
> ) INHERITS ("public"."news")
> WITHOUT OIDS;

Since the fields don't exist in news_001, it has to store them
somewhere - in the table it inherits from.

Inheritence is meant to be used to change something in the
substructure/child table/whatever.

If that object isn't in the child, it has to go back to the parent to
work out what to do (in your case, store the entry).

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

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

Re: [GENERAL] giving users access to specific databases

[chris smith]

On 4/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm hoping someone can tell me how to go about this, or if a solution is
> even possible with my current set up.  I realize this question may go beyond
> pure postgres topics and have to do more with how my hosting company has
> their servers configures, but this group seemed like my best option  for
> help.  If anyone has suggestions on what other groups might be helpful to
> post this question to, I would really appreciate it.
>
> I do my database & application work on a shared Linux server provided by my
> hosting company.  It appears they have just one installation of postgres on
> the server and that all of their customers on that server are able to create
> databases and users under their account.  I'm not quite sure how they have
> enabled specific user accounts for access to my specific part of the server,
> but I do know that when I log in I have access to a cpanel interface, email
> configuration, all of my files on the server, access to create new postgres
> databases and a link to phpPgAdmin.

Do you get the option to create a new database user? You could create
a new user and give that user access to your database.

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

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

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

Re: [GENERAL] How to use result column names in having cause

[chris smith]

On 3/31/06, Andrus <[EMAIL PROTECTED]> wrote:
> >> In real application I have long expression instead of 123 and do'nt want
> >>  repeat this expression in HAVING clause.
> >
> > You have to repeat the expression. "AS" changes the output name, it
> > can't be used either in the where clause or any other limiting factor
> > like 'having':
>
> Doc about HAVING condition says:
>
> Each column referenced in condition must unambiguously reference a grouping
> colum
>
> HAVING x> AVG(bar) unambiguously references to a grouping column x
>
> Is this bug ? It is very tedious to repeat same column expression in a
> multiple times: one time in column expression, and n times in having clause.


But you're not referencing x, you're trying to use AVG(bar) in your expression.


I assume it's this way because the standard says so.. one of the more
knowledgable list members will be able to confirm/deny this.

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

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

Re: [GENERAL] How to use result column names in having cause

[chris smith]

On 3/31/06, Andrus <[EMAIL PROTECTED]> wrote:
> CREATE TEMP TABLE foo( bar integer );
>
> SELECT 123 AS x
>   FROM foo
>   GROUP BY 1
>   HAVING x> AVG(bar)
>
> causes
>
> ERROR:  column "x" does not exist
>
> Why ? How to make this working ?
>
> In real application I have long expression instead of 123 and do'nt want
>  repeat this expression in HAVING clause.

You have to repeat the expression. "AS" changes the output name, it
can't be used either in the where clause or any other limiting factor
like 'having':

test=# create table t1(a int);
test=# insert into t1(a) values (1);
test=# SELECT a AS x from t1 where x=1;
ERROR:  column "x" does not exist

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

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

Re: [GENERAL] about un-discrible

[chris smith]

On 3/29/06, 查海平 <[EMAIL PROTECTED]> wrote:
> hi,
>Could anyone tell me how to un-discrible this mails list? how to do?

Check the mail headers:

List-Unsubscribe: 

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

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

Re: [GENERAL] pg 8.1.2 performance issue

[chris smith]

On 3/26/06, Ed L. <[EMAIL PROTECTED]> wrote:
> On Saturday March 25 2006 9:36 pm, Ed L. wrote:
> > I have a performance riddle, hoping someone can point me in a
> > helpful direction.  We have a pg 8.1.2 cluster using
> > Apache::Sessions and experiencing simple UPDATEs taking
> > sometimes 30+ seconds to do a very simply update, no foreign
> > keys, no triggers:
> >
> > Table "public.sessions"
> >   Column   | Type  | Modifiers
> > ---+---+---
> >  id| character(32) | not null
> >  a_session | text  |
> > Indexes:
> > "sessions_pkey" PRIMARY KEY, btree (id)
>
> The table has 6800 rows over 18000 pages, and is getting a
> minimum of many tens of thousands of updates per day with
> queries like this:

If you're updating that much, how often are you running 'analyze'? Are
you running autovacuum? How often?

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

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

Re: [GENERAL] inheridt tables from db

[chris smith]

On 3/25/06, nik600 <[EMAIL PROTECTED]> wrote:
> hi
>
> i am considering to port an important web applications to postgres,
> this applications is made of php and i reuse the same code for many
> customer, due to have a clean structure and simple updates...
>
> now i have one code and many databases in mysql...
>
> i know that with postgres i can inheridt some properties...can i have
> a main database, called A and then many databases, Customer1,
> Customer2, Customer3 and if i made a change in A the changes is
> replicated to Customer1,2 and 3?

Databases don't have inherited properties, but tables do.

http://www.postgresql.org/docs/8.1/static/tutorial-inheritance.html

http://www.postgresql.org/docs/8.1/static/ddl-inherit.html

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

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

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

Re: [GENERAL] Practical limit on number of tables ina single database

[chris smith]

On 3/24/06, Just Someone <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I am creating a hosted solution that I want to base on separation by
> schemas. So that each hosted family we will have, will have a schema
> assigned to it (and a user). On login I will set the search path, and
> so each family will see it's tables. This is all tested and works
> fine.
>
> But I would like to know if there's a practical limit to the number of
> schemas and tables I can have. Please note that I'm using table spaces
> to make sure the directories are manageable.
>
> I tested it so far with 13000 schemas and users, with 26 tables in
> each schema (a total of more that 33 tables). It works perfectly,
> but I would like to know if someone has experience with this number of
> tables/schemas, and if there's a limit I should be careful of.

There's no real limit. See this recent thread for details:

http://archives.postgresql.org/pgsql-advocacy/2006-03/msg00082.php

and

http://people.planetpostgresql.org/greg/index.php?/archives/37-The-million-table-challenge.html

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

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

Re: [GENERAL] can't create user collumn

[chris smith]

On 3/17/06, loki <[EMAIL PROTECTED]> wrote:
> Hi,
> i'm just starting with postgres DB, but this looks very strange to me:
>
> If i try to create table with collumn user, it fails with error:
> create exec error:ERROR:  syntax error at or near "user" at character 368
>
> query:
> CREATE TABLE Log (
> log_datedateNOT NULL,
> log_timetimeNOT NULL,
> timezoneint NOT NULL default  60 ,
> destvarchar(20) NOT NULL default 'messages',
> hostnamevarchar(100)NOT NULL default 'localhost',
> source  varchar(20) NOT NULL default 'db_speed',
> pid numeric(10) NOT NULL default  0 ,
> categoryint NOT NULL default  10 ,
> priorityint NOT NULL default  10 ,
> userint NOT NULL default  0 ,
> log varchar(800)NOT NULL
> );
>
> But if I change the collumn name to "usr" (just this, nothing else), it
> is o.k. and the table is created.


user is a reserved sql word:

http://www.postgresql.org/docs/8.1/static/sql-keywords-appendix.html

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

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

[GENERAL] full text indexing

[chris smith]

Hi all,

Just wondering which full text module is better & what the differences
are between tsearch and fti ?

The table in question has roughly 80,000 rows.

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

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

Re: [GENERAL] out of memory using Postgres with Spring/Hibernate/Java

[chris smith]

On 3/15/06, maarten roosendaal <[EMAIL PROTECTED]> wrote:
> Hi,
>
> We are currently having a problem that our Postgres DB
> is throwing an SQL error which states that it's 'out
> of memory'.
>
> What we have is a DB with 1 table that has 3.9 million
> records. We need to find certain records that are to
> be processed by a Java App so we do a "select id from
> table where type=a and condition in (1, 2) order by id
> limit 2000". When this query gets executed we see the
> memory on the DB Server increasing and after it has
> finishes it drops a bit but we see it growing a few MB
> per few minutes. This has caused an out of memory
> after the system has been processing for a day or 2.
> The query is heavy because of the order by but that
> does not explain why the memory is increasing.

What does explain show for the query? Are the fields indexed
appropriately? Have you analyzed the table recently?

Postgres needs to store the ordered results somewhere so of course
that explains the memory increase.

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

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

   http://archives.postgresql.org

Re: [GENERAL] Question about index usage

[chris smith]

On 3/7/06, Guido Neitzer <[EMAIL PROTECTED]> wrote:
> Hi.
>
> Is there a reason why this query:
>
> select id from dga_dienstleister where plz in ('45257', '45259');
>
> doesn't use this index:
>
>  "dga_dienstleister_plz_index" btree (plz varchar_pattern_ops)
>
> but uses this index:
>
>  "dga_dienstleister_plz_index2" btree (plz)
>
> I had the first index setup for queries with "plz like '4525%'" but I
> never tested the "in" query until I saw in the logs that these
> queries where slow compared to the rest. Query plans at the end.
>
> cug
>
>
> DGADB=# explain analyse select id from dga_dienstleister where plz
> like
> '45257';   Q
> UERY PLAN
> 
> 
> Bitmap Heap Scan on dga_dienstleister  (cost=2.07..82.41 rows=21
> width=8) (actual time=13.489..14.211 rows=16 loops=1)
> Filter: ((plz)::text ~~ '45257'::text)
> ->  Bitmap Index Scan on dga_dienstleister_plz_index
> (cost=0.00..2.07 rows=21 width=0) (actual time=13.323..13.323 rows=16
> loops=1)
>   Index Cond: ((plz)::text ~=~ '45257'::character varying)
> Total runtime: 14.328 ms
> (5 rows)
>
>
> DGADB=# explain analyse select id from dga_dienstleister where plz =
> '45257';
>QUERY
> PLAN
> 
> ---
> Bitmap Heap Scan on dga_dienstleister  (cost=2.07..82.41 rows=21
> width=8) (actual time=0.486..0.663 rows=16 loops=1)
> Recheck Cond: ((plz)::text = '45257'::text)
> ->  Bitmap Index Scan on dga_dienstleister_plz_index2
> (cost=0.00..2.07 rows=21 width=0) (actual time=0.424..0.424 rows=16
> loops=1)
>   Index Cond: ((plz)::text = '45257'::text)
> Total runtime: 0.826 ms
> (5 rows)
>
>
>
>

Try without the quotes:

select id from dga_dienstleister where plz in (45257, 45259);

What is the table structure for dga_dienstleister ?

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

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

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

Re: [GENERAL] User tables

[chris smith]

> > Suppose there 3 users red, green, blue. How can the user green know what
> > tables he has created?!
> >  From psql command line \dt lists every table in the DB!!!
> >
> > Thanks in advance.
> >
> > Hrishi
> If you mean that the owner of the table(s) is the user "green", then try
> select * from pg_tables where tableowner='green';

or from inside psql:

\z

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

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

Re: [GENERAL] Question about the contrib rpm ?

[chris smith]

On 3/3/06, Agnes Bocchino <[EMAIL PROTECTED]> wrote:
> Hello Tom, hello List,
>
> Sorry if we haven't been clear in our first mail.
> We don't really understand your answer.
> So, we try to clarify our general question and give more details :
>
> When we go on the web site to download PostgreSQL 8.1.2,
> we find not only the serveur rpm but also some others rpms.
> and we don't kow which of them we have to install together with our rpm
> make from the 8.1.2 targz.
>
> We are making our rpm on Novascale Ia64
> We have used the "postgresql-8.1.2.tar.gz" file downloaded from the
> PostGreSQL web site. From that file, we have re-compiled PostGreSQL for
> IA64 on Red Hat Enterprise Linux 4 AS, with the "icc" Intel compiler.
> We would like to 'deliver'  a more complete set as possible.
> and we don't know if we have to package some others packages..
> For the langage python,perl,tcl ...we know that if we need them we
> have to use the --with option when we compile.
> It seems also to us that it is not necessary to have the lib rpm as the
> necessary librairies are include in the rpm when wecompile and package it.
> but ..we dont' know what doing with the *contrib *rpm
> available on the net, should we have to deliver it with our rpm.
>
> Why this question ?
> When we have extract files from the archive file, we have obtained these
> directories :
> [/BUILD/postgresql-8.1.2]$ ls -ltr
> total 1528
> -rw-r--r--   1 postdev pgsql445 Apr 23  2004 aclocal.m4
> -rw-r--r--   1 postdev pgsql   1375 Oct  1  2004 README
> -rw-r--r--   1 postdev pgsql   1412 Oct  6  2004 Makefile
> -rw-r--r--   1 postdev pgsql   1192 Dec 31  2004 COPYRIGHT
> -rw-r--r--   1 postdev pgsql   3435 May  1  2005 GNUmakefile.in
> -rwxr-xr-x   1 postdev pgsql 689752 Jan  5 05:02 configure
> -rw-r--r--   1 postdev pgsql  43596 Jan  5 05:02 configure.in
> -rw-r--r--   1 postdev pgsql 387774 Jan  6 05:09 HISTORY
> -rw-r--r--   1 postdev pgsql  44484 Jan  6 05:09 INSTALL
> drwxr-xr-x   2 postdev pgsql   4096 Jan  6 05:09 config
> drwxr-xr-x  35 postdev pgsql   4096 Jan  6 05:09 *contrib*
> -rw-r--r--   1 postdev pgsql   3435 Feb 16 12:22 GNUmakefile
> -rwxr-xr-x   1 postdev pgsql  56658 Feb 16 12:22 config.status
> drwxr-xr-x  15 postdev pgsql   4096 Feb 16 12:22 src
> drwxr-xr-x   7 postdev pgsql   4096 Feb 16 12:22 doc
> -rw-r--r--   1 postdev pgsql 278305 Feb 16 12:22 config.log
>
> Under the "contrib" repertory, we have among others things
> "*start-scripts*" directory which contains the "linux" file which allows
> to launch automatically PostGreSQL each time the machine reboots.
> [ contrib]# ls
> adddepend  dblink intarray   mSQL-interface
> pgstattupletablefunc
> btree_gist dbmirror   isbn_issn  oid2name
> pg_trgmtips
> chkpassearthdistance  lo oracle
> README tsearch2
> contrib-global.mk  fulltextindex  ltree  pgbench
> seguserlock
> cube   fuzzystrmatch  macpg_buffercache
> spivacuumlo
> dbase  intagg Makefile   pgcrypto
> start-scripts  xml2
>
>
> And when we install our rpm, we don't have a "contrib" directory such as
> this obtains after having extracted the files from the tar.gz archive.
> Our question is how to add in the rpm that we have generated a "contrib"
> directory, in order to have scripts like "linux" ?

If you're building your own rpm you'll need to do it yourself - that's
getting beyond what this list can help you with.

> Or  should we used the "contrib" rpm available in the web site

No - because it's not built for your system or with your compiler, it
may work but you may also run into strange bugs.

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

Re: [GENERAL] createuser permssion for group

[chris smith]

On 2/26/06, Jebus <[EMAIL PROTECTED]> wrote:
> Is it possible to give a group the the createuser permission ? This
> way if a user in the group they can create users.

Version 8.1.x does..

postgres=# \h create group
...

| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER

---(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] Could DBmirror be done with the mechine using RPM installation instaed of souce code [tar.gz] ?

[chris smith]

On 2/26/06, Amrit Angsusing <[EMAIL PROTECTED]> wrote:
> I try to do it by rpm installation but the seemed to miss some file which
> was described by the doc. for ex. pending.so and its' path and
> perl /./DBmirror.pl
> could not run

If you have a problem copy the exact error message, nobody can help
you with that "message".

> Could you suggest me the URL how to install DBmirror by the rpm mechine?

Google?

rpm -ivh /path/to/rpm.rpm

That will install the rpm, then you need to set it up. Read the
appropriate documentation (ie the docs included with dbmirror) on how
to do it.


> 2006/2/25, chris smith <[EMAIL PROTECTED]>:
> > > I use the two  mechines with FC3 and postgresql 8.1 RPM installation and
> > > would like to do DBmirror or other postgresql database replication from
> > > master to slave server . Could I do this replication using RPM or I must
> use
> > > the tar.gz version ? Could anybody suggest me about replication?
> >
> > There should be a 'postgresql-contrib' rpm for FC3 (use your favourite
> > search engine).
> >
> > If you can't find one - create an rpm (it's pretty easy). That's
> > getting way outside of this mailing list's scope though.
> >
>
>

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

Re: [GENERAL] Could DBmirror be done with the mechine using RPM installation instaed of souce code [tar.gz] ?

[chris smith]

> I use the two  mechines with FC3 and postgresql 8.1 RPM installation and
> would like to do DBmirror or other postgresql database replication from
> master to slave server . Could I do this replication using RPM or I must use
> the tar.gz version ? Could anybody suggest me about replication?

There should be a 'postgresql-contrib' rpm for FC3 (use your favourite
search engine).

If you can't find one - create an rpm (it's pretty easy). That's
getting way outside of this mailing list's scope though.

---(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] success with p2p ip connection

[Chris Smith]

Please reply to the same thread you start instead of starting a new one 
every time (choose the last reply and hit "Reply to All").

[EMAIL PROTECTED] wrote:
I looked at the server machine, in a section regarding ip connections, 
and saw that security was set to prevent other machines from connecting, 
so once I set it to no security, I could connect now all I need to 
do is figure out how to define high security, but allow 192.0.0.101 (the 
client machine) so... I am quite pleased to see that it works...
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Baffling sequential scan plan when index scan would

[Chris Smith]

Hi Jeff,
Maybe because the key is a bigint - you're looking for an int (int4).
Try casting it to a bigint ?
Jeffrey W. Baker wrote:
I always thought I would not be the kind of person who writes to this
list asking why the planner is using a sequential scan.  I always looked
upon such people as newcomers who would eventually learn the mysterious
wonders of the Pg query execution planner.  

But really, this plan is bizarre!  Why is it scanning sequentially for
ONE tuple as selected by the primary key?  I even increased stats to
1000 and disable seq_scan, but it still insists it cannot do an index
scan.
skunk=# \d items;
  Table "items"
   Column   |   Type   | Modifiers 
+--+---
 item   | bigint   | not null
[...]
Indexes:
"items_pkey" primary key, btree (item)

skunk=# analyze verbose items;
INFO:  analyzing "items"
INFO:  "items": 80372 pages, 30 rows sampled, 2660996 estimated total rows
ANALYZE
skunk=# explain analyze select * from items where item = 2143888;
  QUERY PLAN   
---
 Seq Scan on items  (cost=1.00..100113634.45 rows=1 width=115) (actual time=4034.564..8859.082 rows=1 loops=1)
   Filter: (item = 2143888)
 Total runtime: 8859.160 ms
(3 rows)

 enable_hashagg | on
 enable_hashjoin| on
 enable_indexscan   | on
 enable_mergejoin   | on
 enable_nestloop| on
 enable_seqscan | off <===
 enable_sort| on
 enable_tidscan | on
 random_page_cost   | 1
 cpu_index_tuple_cost   | 0.001
 cpu_operator_cost  | 0.0025
 cpu_tuple_cost | 0.01
What's even more baffling is the planner will use index scan for any
other indexed column, including columns for which the index is not
particularly selective, like item category or date:
skunk=# set enable_seqscan=on;
SET
skunk=# explain select * from items where category = 245;
  QUERY PLAN  
--
 Index Scan using items_cat_idx on items  (cost=0.00..69887.77 rows=125795 width=115)
   Index Cond: (category = 245)
(2 rows)

skunk=# explain select * from items where startdate = '2005-03-01';
  QUERY PLAN  
--
 Index Scan using items_start_ids on items  (cost=0.00..1948.53 rows=30283 width=115)
   Index Cond: (startdate = '2005-03-01'::date)
(2 rows)

So it seems that an index scan returning a half-million tuples is OK,
but an index scan returning a single tuple is right out.  What?
-Confused in California
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
--
Regards,
Chris Smith
 Unit 2, 3 National Street, Rozelle, NSW 2039 Australia
Ph: +61 2 9555 5570
Fx: +61 2 9555 5571
email: [EMAIL PROTECTED]
web: http://www.interspire.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] using limit with delete

[Chris Smith]

Hi all,
I'm trying to use a limit clause with delete, but it doesn't work at the 
moment (are there plans to add this - I could try to do up a patch ?).

eg.
delete from table where x='1' limit 1000;
Is there another way to approach this?
I'm trying to delete records through a webapp and if there are 500,000 
records for example, I can't really leave the page open and expect it to 
finish...

--
Regards,
Chris Smith
 Unit 2, 3 National Street, Rozelle, NSW 2039 Australia
Ph: +61 2 9555 5570
Fx: +61 2 9555 5571
email: [EMAIL PROTECTED]
web: http://www.interspire.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] sequence advances on failed insert

[Chris Smith]

Yes, he meant that one should not assume that the next value will be one 
increment higher than the current highest value in the table.

You shouldn't rely on them being sequential because they will not always 
be that way.

Sven Willenberger wrote:

David Fetter presumably uttered the following on 04/07/05 20:16:
On Thu, Apr 07, 2005 at 07:59:52PM -0400, Matthew Terenzio wrote:
I'm noticing that a sequence is advancing even if the insertion
fails.  Is this weird or expected?

It's expected.  Sequences are guaranteed to generate unique IDs.
These happen to be an increasing sequence of integers, but there is no
attempt to make this a gap-free sequence, and your apps should not
depend on the actual value of said ID.

I assume by "not depend on the actual value" that one should not assume 
that the next value will be one increment higher than the current 
highest value in the table; because it is guaranteed to be unique, I 
would think it to be an excellent way to assign a customer id, for 
example, which can then be referenced (foreign key, etc) by other tables 
after a new record is added. Unless there is some other reason one 
should not use a sequence value as any type of identifier?

Sven
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match
--
Regards,
Chris Smith
 Unit 2, 3 National Street, Rozelle, NSW 2039 Australia
Ph: +61 2 9555 5570
Fx: +61 2 9555 5571
email: [EMAIL PROTECTED]
web: http://www.interspire.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] using limit with delete

[Chris Smith]

Hi Csaba,
Because I have a where clause limiting which records I'm deleting.
I'm deleting old info from a database, so I'm doing:
DELETE FROM sessions WHERE EXISTS (SELECT sessiontime FROM sessions 
WHERE sessiontime < (timenow-7days) LIMIT 100)

(timenow-7days is evaluated in PHP and made an int).
So every time the page gets hit, I'm deleting up to 100 records that are 
older than 7 days..

Csaba Nagy wrote:
Hi Chris,
Just a thought: if you have to clear the table anyway, wouldn't it work
for you to use truncate ? That should be faster than delete.
HTH,
Csaba.
On Thu, 2005-04-07 at 04:11, Chris Smith wrote:
I don't care about the order in my particular case, just that I have to 
clear the table.

I'll try the subquery and see how I go :)
Thanks!
Neil Conway wrote:
Chris Smith wrote:

I'm trying to use a limit clause with delete, but it doesn't work at 
the moment

It isn't in the SQL standard, and it would have undefined behavior: the 
sort order of a result set without ORDER BY is unspecified, so you would 
have no way to predict which rows DELETE would remove.


delete from table where x='1' limit 1000;

You could use a subquery to achieve this:
DELETE FROM table WHERE x IN
   (SELECT x FROM table ... ORDER BY ... LIMIT ...);
-Neil
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
  (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


--
Regards,
Chris Smith
 Unit 2, 3 National Street, Rozelle, NSW 2039 Australia
Ph: +61 2 9555 5570
Fx: +61 2 9555 5571
email: [EMAIL PROTECTED]
web: http://www.interspire.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] using limit with delete

[Chris Smith]

For the archives...
Using 7.4 so IN() is a little slower, so I rewrote it slightly to be
DELETE FROM table WHERE EXISTS (select x from table  LIMIT ...);
Works very nicely :)
Thanks again.
Neil Conway wrote:
Chris Smith wrote:
I'm trying to use a limit clause with delete, but it doesn't work at 
the moment

It isn't in the SQL standard, and it would have undefined behavior: the 
sort order of a result set without ORDER BY is unspecified, so you would 
have no way to predict which rows DELETE would remove.

delete from table where x='1' limit 1000;

You could use a subquery to achieve this:
DELETE FROM table WHERE x IN
(SELECT x FROM table ... ORDER BY ... LIMIT ...);
-Neil
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Regards,
Chris Smith
 Unit 2, 3 National Street, Rozelle, NSW 2039 Australia
Ph: +61 2 9555 5570
Fx: +61 2 9555 5571
email: [EMAIL PROTECTED]
web: http://www.interspire.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] using limit with delete

[Chris Smith]

I don't care about the order in my particular case, just that I have to 
clear the table.

I'll try the subquery and see how I go :)
Thanks!
Neil Conway wrote:
Chris Smith wrote:
I'm trying to use a limit clause with delete, but it doesn't work at 
the moment

It isn't in the SQL standard, and it would have undefined behavior: the 
sort order of a result set without ORDER BY is unspecified, so you would 
have no way to predict which rows DELETE would remove.

delete from table where x='1' limit 1000;

You could use a subquery to achieve this:
DELETE FROM table WHERE x IN
(SELECT x FROM table ... ORDER BY ... LIMIT ...);
-Neil
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Regards,
Chris Smith
 Unit 2, 3 National Street, Rozelle, NSW 2039 Australia
Ph: +61 2 9555 5570
Fx: +61 2 9555 5571
email: [EMAIL PROTECTED]
web: http://www.interspire.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] using limit with delete

[Chris Smith]

Hi all,
I'm trying to use a limit clause with delete, but it doesn't work at the 
moment (are there plans to add this - I could try to do up a patch ?).

eg.
delete from table where x='1' limit 1000;
(so truncate is out - I have a 'where' clause).
Is there another way to approach this?
I'm trying to delete records through a webapp and if there are 500,000 
records for example, I can't really leave the page open and expect it to 
finish...

--
Regards,
Chris Smith
 Unit 2, 3 National Street, Rozelle, NSW 2039 Australia
Ph: +61 2 9555 5570
Fx: +61 2 9555 5571
email: [EMAIL PROTECTED]
web: http://www.interspire.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Referencing created tables fails with message that

[Chris Smith]

Try putting quotes around the table name:
select * from "Table";
?
Do you see it in the table list?
\dt
Tommy Svensson wrote:
I have just installed Postgresql and tried it for the first time.
One very serious problem I ran into was when actually trying to use 
created tables.
Creating a simple table without any foreign keys works OK, but after 
creating the
table it is not possible to do a select on it! I tried the following 
variants:

SELECT * FROM ;
SELECT * FROM public.;
SELECT * FROM .public.;
All result in the message "The relation  does not exist!" or "The 
relation public. does not exist!".

Creating a new table with a foreign key referencing the first table is 
also impossible due to exactly the same error message!

This behaviour is the same using  DBVisualizer/jdbc or psql.
So the question is how do you actually reference the tables you have 
created so that postgres will find them ?
The tables do actually get created. I can se them in DBVisualizer.

I'm using version 7.4.5 on Linux Mandrake 10.1.
Best Regards,
Tommy Svensson
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org
--
Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] [OT] Duration between two timestamps

[Chris Smith]

Hi Phil,
Have you tried copying the insert statement straight into postgres? 
print it out (or log it or whatever) and try that first.. that'll narrow 
things down a little.

phil campaigne wrote:
Hi All,
In my java application I need to subtract two java.sql.timestamps. and I 
want to store the result as sql type "interval".
But my insert statement is failing.

Does anyone know what java  type I need to use in the insert statement? 
(it must accept null values)
thanks in advance,
Phil

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
---(end of broadcast)---
TIP 3: 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] Easy transaction question

[Chris Smith]

You can issue them one at a time - however you can't have a transaction 
that spans multiple pages (ie you can't start it on index.php and finish 
it on end.php).

Rick Schumeyer wrote:
A question about using transactions from php:
 

Does the entire transaction have to be sent all at once,
or can I begin the transaction, issue commands one
at a time, and then end the transaction?
--
Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] sorting problem

[Chris Smith]

Would doing it this way require an index:
create index lower_lastname on table x lower(lastname);
?
Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
Michael Fuhr wrote:
On Fri, Dec 17, 2004 at 11:28:36AM +1100, Jamie Deppeler wrote:

Problem i am having at the moment i cant get a true alpha sort to work 
as Order By is sorting A..Z then a..z where i need aA..zZ sort 
independant of case.

ORDER BY LOWER(person.lastname)
or
ORDER BY UPPER(person.lastname)

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] MD5

[Chris Smith]

(Ignore my other reply.. ;P)
You'll need to import the pgcrypto.sql file (this creates the functions 
for you).

Read the doco on how to install the extension, it should tell you where 
the sql file is.

Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
Jamie Deppeler wrote:
Tino Wildenhain wrote:
Hi,
Am Freitag, den 17.12.2004, 09:41 +1100 schrieb Jamie Deppeler:
 

Hi,
I was just wondering is it possible to encrypt a filed in the database 
with md5? i know it is possible to do it with DB users
   

No. You cannot encrypt with md5 because you cant decrypt.
md5 is a hash function. But you can use it any time to
hash values you insert.
Regards
Tino

 

Well basically i want to store and hashed value that will never be 
changed just compaired too hashed values

also when i try to encrpyt a field i get this error 
encypt(text,"unknown","unknown")


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] MD5

[Chris Smith]

Use the md5 function:
select md5('welcome');
   md5
--
 40be4e59b9a2a2b5dffb918c0e86b3d7
(1 row)
Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
Jamie Deppeler wrote:
Tino Wildenhain wrote:
Hi,
Am Freitag, den 17.12.2004, 09:41 +1100 schrieb Jamie Deppeler:
 

Hi,
I was just wondering is it possible to encrypt a filed in the database 
with md5? i know it is possible to do it with DB users
   

No. You cannot encrypt with md5 because you cant decrypt.
md5 is a hash function. But you can use it any time to
hash values you insert.
Regards
Tino

 

Well basically i want to store and hashed value that will never be 
changed just compaired too hashed values

also when i try to encrpyt a field i get this error 
encypt(text,"unknown","unknown")


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Debian Packages for Postgresql 8.0.0 RC1

[Chris Smith]

I doubt you'll find any for an RC release... when 8 is released (final), 
then I think you'll find them.

Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
Simon Wittber wrote:
Despite 30 minutes of googling, I am unable to find any debian
packages for Postgresql 8.0.0 RC1.
Does anyone know where I might obtain them?
Sw.
---(end of broadcast)---
TIP 8: explain analyze is your friend


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Insert do not work in my case

[Chris Smith]

The easiest way to start is to turn on query logs for your server and 
see if it gets that far.

If it does - copy the query into psql and run it manually - it will tell 
you if there's a problem.

If it doesn't get that far, then that's another problem altogether...
Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com
Mickael Remond wrote:
Hello,
I am running an application on Postgresql 8.0.0 rc1. The application is 
running on JBoss 4.0.1RC2 and I am using the JDBC driver 
pg80b1.308.jdbc3.jar.

My problem is that insert that are sent to the database through a 
prepared statement are not written in the database. The application is 
doing a subsequent read and fail. A simple select from psql show that 
the table is stil empty.
The log in debug level mode 3 are showing no particular problem. I 
clearly see the prepared statement (Not complete but with question marks 
instead of variable). I then see the select but the read is failing in 
the application.

I really do not see why this is happening. I suspect a configuration 
problem but does not see what I should change.

The application was working properly on PostgreSQL 7.2.
Do you have any clue ?

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.4 - Release Date: 12/15/2004
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] postgresql and javascript

[Chris Smith]

[EMAIL PROTECTED] wrote:
Does anyone know how to connect  javascript to a postgresql database
You can't connect javascript to any sort of database. You need something 
like php, python etc - it can connect to your database and generate 
javascript.

Regards,
Chris Smith
Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia
Ph: +61 2 9517 2505
Fx: +61 2 9517 1915
email: [EMAIL PROTECTED]
web: www.interspire.com

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] rewrite count distinct query

[Chris Smith]

Hi all,
'Scuse the long post :) I'm trying to include all relevant info..
I'm trying to work out a better way to approach a query, any tips are 
greatly appreciated.

The relevant tables:
db=# \d tp_conversions
Table "public.tp_conversions"
Column |  Type  | Modifiers
---++
 conversionid  | integer| not null default 0
 type  | character varying(10)  |
 name  | character varying(255) |
 amount| double precision   |
 cookieid  | character varying(32)  |
 currtime  | integer|
 ip| character varying(20)  |
 origintype| character varying(20)  |
 originfrom| character varying(255) |
 origindetails | character varying(255) |
 userid| integer|
Indexes:
"tp_conversions_pkey" primary key, btree (conversionid)
"conv_origindetails" btree (origindetails)
"conv_originfrom" btree (originfrom)
"conv_origintype" btree (origintype)
"conv_time" btree (currtime)
"conv_userid" btree (userid)
trackpoint=# SELECT count(*) from tp_conversions;
 count
---
   261
(1 row)
db=# \d tp_search
Table "public.tp_search"
  Column  |  Type  | Modifiers
--++
 searchid | integer| not null default 0
 searchenginename | character varying(255) |
 keywords | character varying(255) |
 currtime | integer|
 ip   | character varying(20)  |
 landingpage  | character varying(255) |
 cookieid | character varying(32)  |
 userid   | integer|
Indexes:
"tp_search_pkey" primary key, btree (searchid)
"search_cookieid" btree (cookieid)
"search_keywords" btree (keywords)
"search_searchenginename" btree (searchenginename)
"search_userid" btree (userid)
trackpoint=# SELECT count(*) from tp_search;
 count
---
  5086
(1 row)

What I'm trying to do...
Work out the number of conversions for each search origin.
This query works:
select
count(distinct conversionid) as convcount,
count(distinct searchid) as searchcount,
(count(distinct conversionid) / count(distinct searchid)) as perc,
s.searchenginename
from tp_conversions c, tp_search s
where
c.origintype='search' and s.searchenginename=c.originfrom and 
s.userid=c.userid and c.userid=1
group by searchenginename
order by convcount desc;

 convcount | searchcount | perc | searchenginename
---+-+--+--
15 |2884 |0 | Google
 1 | 110 |0 | Google AU
 2 | 308 |0 | Google CA
 1 |  25 |0 | Google CL
 1 | 143 |0 | Google DE
 1 | 117 |0 | Google IN
 1 |  26 |0 | Google NZ
 3 |  49 |0 | Google RO
 1 |  60 |0 | Google TH
 2 | 174 |0 | Yahoo
(10 rows)
However the percentage is wrong.
I can cast one to a float:
(count(distinct conversionid) / count(distinct searchid)::float)
and it'll give me a better percentage:
 convcount | searchcount |perc | searchenginename
---+-+-+--
15 |2884 | 0.00520110957004161 | Google
 3 |  49 |  0.0612244897959184 | Google RO
 2 | 308 | 0.00649350649350649 | Google CA
 2 | 174 |  0.0114942528735632 | Yahoo
 1 | 110 | 0.00909090909090909 | Google AU
 1 |  25 |0.04 | Google CL
 1 | 143 | 0.00699300699300699 | Google DE
 1 | 117 | 0.00854700854700855 | Google IN
 1 |  26 |  0.0384615384615385 | Google NZ
 1 |  60 |  0.0167 | Google TH
(10 rows)
(I think the answer to this is 'no' but I'm going to ask anyway :P)
Is there an easier way to get the more-detailed percentage (it's meant 
to work in multiple databases - so casting to a float won't work for 
other db's) ?

More importantly... Is there a better way to write the query (I don't 
like the count(distinct...) but it works and gives the right info) ?

I tried to do it with a union:
SELECT
count(searchid),
searchenginename
from tp_search s
where userid=1
group by searchenginename
union
select
count(conversionid),
originfrom
from tp_conversions c
where c.userid=1
group by originfrom;
but then realised that getting the data out with php would be a 
nightmare (plus I can't get the percentages).

Lastly:
db=# SELECT version();
version
---
 PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
(I know it's a little out of date, upgrading later this week).
Any suggestions/hints/tips welcome :)
Thanks,
C

Re: [GENERAL] Interpreting query plan

[Chris Smith]

Stephan,

Thanks for your reply.  Here is the output of "explain analyze".  I also
replaced by simple values with a real query that gets run and (according to
our profiling) takes a long time.  However, the query is now taking a much
shorter period of time than it was profiled at by the application.  I can only
guess the difference has something to do with system load.  I could try to run
this again during a high-load period, if that is necessary.  Let me know.

miqweb=> explain analyze select distinct t0.* from UserAccount t0, UserMapping
t1 where (t0.companyid = 628) and ((t0.companyid = 628) and (t0.userid =
t1.use
rid) and (t1.groupid in
(628,948,949,950,951,953,954,1272,1279,1296,1299,1300,1
363,1423,1446,1467,1526,1724,1735,1759,1763,1772,1785,1841,1862,1975,2721,2800
,
2801,2802,2803,1264,1394,1525,1662,1843,1844,1845,1396,1528,1860,1846,1762,242
2
,1271,1847,1848,1281,1849,1850,1851,1266,1809,1852,1853,2421,1854,1855,1913,18
5
6,1857,1269,1268,1858,1859,2804))) and (t0.companyid = 628);

[...]

 Unique  (cost=952.15..959.37 rows=289 width=55) (actual time=137.130..143.363
r
ows=752 loops=1)
   ->  Sort  (cost=952.15..952.87 rows=289 width=55) (actual
time=137.123..138.0
04 rows=1328 loops=1)
 Sort Key: t0.userid, t0.companyid, t0.username, t0."password",
t0.isact
ive, t0.isregistered, t0.lastlogin, t0.firstname, t0.lastname
 ->  Hash Join  (cost=869.15..940.34 rows=289 width=55) (actual
time=112
.112..130.948 rows=1328 loops=1)
   Hash Cond: ("outer".userid = "inner".userid)
   ->  Seq Scan on useraccount t0  (cost=0.00..55.71 rows=629
width=
55) (actual time=0.239..8.501 rows=753 loops=1)
 Filter: (companyid = 628)
   ->  Hash  (cost=866.28..866.28 rows=1151 width=4) (actual
time=11
1.762..111.762 rows=0 loops=1)
 ->  Seq Scan on usermapping t1  (cost=0.00..866.28
rows=115
1 width=4) (actual time=4.251..109.563 rows=1328 loops=1)
   Filter: ((groupid = 628) OR (groupid = 948) OR
(group
id = 949) OR (groupid = 950) OR (groupid = 951) OR (groupid = 953) OR (groupid
=
 954) OR (groupid = 1272) OR (groupid = 1279) OR (groupid = 1296) OR (groupid
=
1299) OR (groupid = 1300) OR (groupid = 1363) OR (groupid = 1423) OR (groupid
=
1446) OR (groupid = 1467) OR (groupid = 1526) OR (groupid = 1724) OR (groupid
=
1735) OR (groupid = 1759) OR (groupid = 1763) OR (groupid = 1772) OR (groupid
=
1785) OR (groupid = 1841) OR (groupid = 1862) OR (groupid = 1975) OR (groupid
=
2721) OR (groupid = 2800) OR (groupid = 2801) OR (groupid = 2802) OR (groupid
=
2803) OR (groupid = 1264) OR (groupid = 1394) OR (groupid = 1525) OR (groupid
=
1662) OR (groupid = 1843) OR (groupid = 1844) OR (groupid = 1845) OR (groupid
=
1396) OR (groupid = 1528) OR (groupid = 1860) OR (groupid = 1846) OR (groupid
=
1762) OR (groupid = 2422) OR (groupid = 1271) OR (groupid = 1847) OR (groupid
=
1848) OR (groupid = 1281) OR (groupid = 1849) OR (groupid = 1850) OR (groupid
=
1851) OR (groupid = 1266) OR (groupid = 1809) OR (groupid = 1852) OR (groupid
=
1853) OR (groupid = 2421) OR (groupid = 1854) OR (groupid = 1855) OR (groupid
=
1913) OR (groupid = 1856) OR (groupid = 1857) OR (groupid = 1269) OR (groupid
=
1268) OR (groupid = 1858) OR (groupid = 1859) OR (groupid = 2804))
 Total runtime: 144.690 ms
(11 rows)


-- 
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation


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

[GENERAL] Interpreting query plan

[Chris Smith]

;RI_ConstraintTrigger_255940" AFTER DELETE ON useraccount FROM adminvisit
NO
T DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noactio
n_del"('adminvisit_fk1', 'adminvisit', 'useraccount', 'UNSPECIFIED', 'userid',
'
userid')
"RI_ConstraintTrigger_255941" AFTER UPDATE ON useraccount FROM adminvisit
NO
T DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noactio
n_upd"('adminvisit_fk1', 'adminvisit', 'useraccount', 'UNSPECIFIED', 'userid',
'
userid')


miqweb=> \d usermapping
  Table "public.usermapping"
 Column  |  Type   | Modifiers
-+-+---
 userid  | integer | not null
 groupid | integer | not null
Foreign-key constraints:
"$1" FOREIGN KEY (userid) REFERENCES useraccount(userid)
"$2" FOREIGN KEY (groupid) REFERENCES groups(groupid)

-- 
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation


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

[GENERAL] Wire protocol v3 details

[Chris Smith]

Hi,

I'm attempting to port some JDBC driver modifications to 7.4 so I can migrate
an application.  Where can I find an overview of the wire protocol v3?

-- 
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Very long time to commit or close connections

[Chris Smith]

Oops, meant to copy the list on this...

- Original Message - 
From: "Chris Smith" <[EMAIL PROTECTED]>
To: "Tom Lane" <[EMAIL PROTECTED]>
Sent: Monday, January 19, 2004 8:01 AM
Subject: Re: [GENERAL] Very long time to commit or close connections


> > There are not that many things happening during connection close.  Does
> > your app use temp tables?  LISTEN/NOTIFY?
>
> Nope.  It's actually a very simple application.  It uses plain select,
insert,
> update, and delete statements with nothing particularly unusual beyond that.
>
> > Nope ... how about looking at the contents of the pg_locks system view
> > when this happens?  What external conditions correspond to the instant
> > when the hangs are released?  (I do not believe that it just happens by
> > magic --- look for background cron jobs starting or finishing at that
> > time, eg a cron job that issues VACUUMs.)
>
> Hmm... I will definitely look.  I pretty much know that it's not a vacuum.
I
> suppose I could have our system monitor insert information from the 'ps'
> utility when there are outstanding connections.
>
> > What PG version is this, anyway?
>
> 7.3.2.  We could upgrade within the 7.3 series, but have not qualified the
> application on 7.4 at this point.  (there are complications, mostly because
of
> a patch we apply to the JDBC drivers, which change for 7.4).
>
> -- 
> www.designacourse.com
> The Easiest Way to Train Anyone... Anywhere.
>
> Chris Smith - Lead Software Developer/Technical Trainer
> MindIQ Corporation
>


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

Re: [GENERAL] Very long time to commit or close connections

[Chris Smith]

Erwin,

> Could this be related to a network problem ?
>
> Like RST not received by the client, making it believe the server did
> not acknowledge the connection.close(), eating up the server's resources
> ? No firewall/VPN gateways between you and your customer ?

Nice thought, but the app and database are on the same box, and communicating
via TCP/IP on the localhost address.  I'm not sure it's even possible to set
up a firewall there, and I'm certain it hasn't been done.

Thanks,

-- 
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation


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

   http://archives.postgresql.org

[GENERAL] Re: Database Recovery Help...

[Chris Smith]

Hi,

Did you try what it suggests?

If you're sure PostgreSQL isn't already running, remove the 
"/tmp/.s.PGSQL.5432" file, and try again.

> I recently came across a problem wherein I am not able to start my
> postgreSQL 7.0.3 server.
>
> Here's what happens..;-(
>
> postgres@kahoy /root$ postmaster -D /var/lib/pgsql/data
> FATAL: StreamServerPort: bind() failed: Permission denied
> Is another postmaster already running on that port?
> If not, remove socket node (/tmp/.s.PGSQL.5432) and retry.
> /usr/bin/postmaster: cannot create UNIX stream port
>
> There is no one binding the 5432 port, and no one is trying to connect
> to that port.  I may be left with no other alternative and restart from
> scratch...;-(
>
> There are some pretty important data currently in the database that I'd
> like to recover...
>
> Is there any way wherein I could recover or reconstruct the data?

--
 Chris Smith
http://www.squiz.net

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Re: Report Writer for PostgreSQL

[Chris Smith]

Hey,

> Does anyone have experience with report writers?  It's not something I
> know much about yet.

Crystal Reports (www.seagate.com) is quite good, can do any sort of report 
you're after. You can use an ODBC driver for accessing the database, so 
accessing pgsql wouldn't be a problem..

Oops just read the last comment, it is a bit expensive.
Maybe not then :)

>  Original Message 
> Subject: Re: [ANNOUNCE] New PostgreSQL Review at epinions.com
> Date: Thu, 26 Apr 2001 21:19:08 -0400 (EDT)
> From: Patrick Lanphier <[EMAIL PROTECTED]>
> To: Justin Clift <[EMAIL PROTECTED]>
>
> Well it needs the capability format data on many different graph,
> capable
> of generating HTML, PDF, and RTF formats.  The server will be running on
> Linux but the design platform can be whatever.  The problem I had with
> one
> report writer was the data from the database was present one way and it
> was not capable on rotating the data for the graph and I wasn't about to
> do this for the report writer.  Is there somebody I should contact that
> you know about a report writer?
>
> Patrick Lanphier
> The Artemis Group
> http://www.artemisgroup.com
>
> On Fri, 27 Apr 2001, Justin Clift wrote:
> > No problem Patrick.  :-)
> >
> > Two questions :
> >
> > a) Which operating system(s) does it need to run on?
> >
> > b) What features does it need to have?
> >
> > Honestly, I haven't done much with report writing, so I'm not going to
> > be the best person to ask.  BUT if you do a quick subscribe to the
> > [EMAIL PROTECTED] mailing list and ask there, many capable
> > people are around.  :-)
> >
> > (You subscribe by sending "subscribe" as a message to
> > [EMAIL PROTECTED]  Unsubscribing later on is the
> > same, but sending "unsubscribe" )
> >
> > :-)
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> > Patrick Lanphier wrote:
> > > Sorry to grab your email address and ask you this.  But I have been
> > > looking for an inexpensive report writer that is feature rich.  What
> > > are your thoughts?
> > >
> > > Patrick Lanphier
> > > The Artemis Group
> > > http://www.artemisgroup.com

--
 Chris Smith
http://www.squiz.net

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

http://www.postgresql.org/users-lounge/docs/faq.html

[GENERAL] pg_ctl restart?

[Chris Smith]

Hey all,

I've been playing around with pg_ctl, and noticed that for pg_ctl
restart it doesn't seem to like the -D parameter the way I'm doing it..

What's the correct way? (This is for v7.1B4).

pgsql@elrond:~/bin$ ./pg_ctl restart -D /usr/local/pgsql/data -s -m fast
pg_ctl: cannot find /usr/local/pgsql/data/postmaster.pid
Is postmaster running?
starting postmaster anyway
pgsql@elrond:~/bin$ /usr/local/pgsql/bin/postmaster: invalid argument -- '-D'
Try '/usr/local/pgsql/bin/postmaster --help' for more information.


I do it without the -D parameter & a correct error message appears

pgsql@elrond:~/bin$ ./pg_ctl restart -s -m fast
pg_ctl: no database directory or environment variable $PGDATA is specified
Try 'pg_ctl --help' for more information.
pgsql@elrond:~/bin$

The other switches for pg_ctl work (start & stop), just restart having the 
problems...

--
 Chris Smith
http://www.squiz.net

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

http://www.postgresql.org/search.mpl