date:20060711

[EMAIL PROTECTED] [EMAIL PROTECTED] writes:
 Is it possible to compile-link together frontend pg_dump code with
 backend code from copy.c?

No.  Why do you think you need to modify pg_dump at all?

regards, tom lane

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

Re: [GENERAL] US Telephone Number Type

2006-07-11 Thread Martijn van Oosterhout

On Tue, Jul 11, 2006 at 01:27:49AM -0400, Alvaro Herrera wrote:
 But I think the main problem may be getting our calling conventions
 right.  I mean, how would you do a PG_GETARG_BOOL() or stuff like that?  
 Maybe if we offered PG_GETARG_DATUM and PG_RETURN_DATUM equivalents in
 PL/Perl we could offer I/O functions there.

Not sure what you're getting at, type input/output functions are no
different than other functions.

Historically I beleive the issue was that languages couldn't handle the
cstring type because it was special. As of recent releases that's not
a problem anymore (though pl/pgsql still doesn't understand it for
example).

Another issue was that there was a special hack to create type
input/output functions because of the chicken/egg issue of type
creation. With explicit shell types this is fixed also (in -HEAD).

AIUI, pl/java can do it. For the others I just don't think people have
really tried...

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


signature.asc
Description: Digital signature

Re: [GENERAL] having indexes when clearing tables

2006-07-11 Thread Csaba Nagy

Nuno,

It's the foreign keys that point to this table are the problem, not the
foreign keys of the table itself. In other words, the child tables for
which the table you delete from is the parent... do you have indexes on
the foreign keys of the child tables which point to this table ? Those
indexes will help... on the other hand, I do have here a few tables
which are at the top of the FK chains, and deleting from them takes ages
even if all the child tables are indexed for the foreign keys... it all
depends on the size of the child tables, the number of them, if the
deletion cascades or not and if yes how many rows of the child tables
are deleted as well... My top table causes cascaded deletes in as much
as 30 other tables, and it actually causes deletion of lots of rows in
there, so I do expect it to work slow... maybe that's what you see too.
Here we actually do delete first from the child tables and then delete
from the parent, so the whole thing can be done in smaller transactions.

Cheers,
Csaba.



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

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

[GENERAL] Error code 1063

2006-07-11 Thread Robert Fitzpatrick

We have PostgreSQL 8.1 running on Windows 2000 for a few weeks now. Don't know 
what happened, the users reported a connection issue to the database and I 
found the service will not start. When
we try to start the service, it could not start claiming no error
returned. So, I go to the command prompt and run the following:

C:\Program Files\PostgreSQL\8.1\bin\pg_ctl.exe runservice -N pgsql-8.1 -D 
C:\Program Files\PostgreSQL\8.1\data\
pg_ctl: could not start service psql-8.1: error code 1063

I tried to Google that error code, but come up with nothing. Can someone
tell us what this code means?

Last few things in the logs is:

2006-06-12 08:50:18 LOG:  autovacuum: processing database postgres
2006-06-12 08:51:18 LOG:  autovacuum: processing database ohc
2006-06-12 08:51:18 LOG:  received fast shutdown request
2006-06-12 08:51:18 LOG:  shutting down
2006-06-12 08:51:19 LOG:  database system is shut down
2006-06-12 08:51:19 LOG:  logger shutting down

I guess it has been down sine then, but the users have not used the database.

-- 
Robert


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

Re: [GENERAL] encoding bug or feature?

2006-07-11 Thread marcelo Cortez

Jorge 

 my test, i created one utf8 database but don´t work

 ideas?
 
  

testutf8=# \set
VERSION = 'PostgreSQL 8.1.0 on i386-pc-linux-gnu,
compiled by GCC cc (GCC) 3.3.5
 (Debian 1:3.3.5-13)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = ' '
DBNAME = 'testutf8'
USER = 'marcelo'
HOST = '/var/run/sume'
PORT = '5432'
ENCODING = 'UTF8'
HISTSIZE = '500'
testutf8=# select upper('ñ');
ERROR:  invalid UTF-8 byte sequence detected near byte
0xf1
testutf8=#


 ideas?
 best regards
  MDC



 
Esa persona especial te espera en Yahoo! Encuentros. 
¡Dejate encontrar! 
http://ar.encuentros.yahoo.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] US Telephone Number Type


On 10 Jul 2006 10:33:52 -0700, Karen Hill [EMAIL PROTECTED] wrote:

Hello,

How would one go about creating a US telephone type in the format of
(555)-555- ?  I am at a loss on how it could be accomplished in
the most correct way possible while not going into the various
different country styles e.g. +01 (555) 555-.

Is the difficulty of creating a telephone type the reason it is not in
postgresql already?


if it was me, i would keep a telephone type to simple text field.
while there is some merit to throwing a domain constraint on it,
history tells me this is more troulbe than it's worth :).

otoh, you could make a small immutable sql based regex function to
attempt to extract the area code or some other number from the text
field.  You could then index this if desired.

merlin

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

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

Re: [GENERAL] [pgadmin-support] Emergency - postgre is not working

2006-07-11 Thread Harald Armin Massa

Anika,Sorry, I dont know what time zone you are in - I usually go to bed 4am and am up 8am in my timezone... I do not know programming AT ALL - just html I also dont know how to put the mail back on the list...sorry.
I am in CEST (central europe summer time) ... just giving that info, so you do not panic if it takes time with the answer.You can copy the mail to the list by hitting reply all, or putting additionally
pgsql-general@postgresql.org into the to field-
When I run the services(local) - there is a line for
postgre SQL database - automatic(startup typle)automatic -(log on as) .\userpostgres90.When I check the property of the line--this is what is on path to executable:

C:\Program Files\PostgreSQL\8.0\bin\pg_ctl.exe runservice -N pgsql-8.0 -D C:\Program Files\PostgreSQL\8.0\data\Okay, fine. You have a standard install on PostgreSQL
8.0When I try and start postgre from the services.msc page - I get the error1069 - could not start logon error.
Propblem gets clearer. I had similiar symptoms before; so my rough guess is: your postgres user did loose a privilege.Technical background:the postgres-account is a low privileged user, which logs on automatically when the service starts up. So he needs the privilege log on as service
And I think that your postgres account loosed that privilege.So I recommend to try the following steps:a) startup the Editor of user accounts (within control panel)b) change the password of the postgres user account to something you know
c) restart services.msc, and open the Properties of the Postgres Servicesd) go to the logon page, and enter the new password you just gave to this account (step b)e) try to start the service again from the service control panel
With entering the logon information again to the logon-tab of the service properties dialog, you are re-giving that privilege.Be aware that group policies within Windows Domains / Active Directory can take away that privilige if not configured correctly! I do not know if you are within Active Directory, if you are, speak to your ActiveDirectory Adminstrator!
I use postgre to run Sam Broadcaster ..which runs 24/7 - but initally all I had to do was click on my sam icon and pow it starts - now even when I click on sam the new screen that pops up is run as. with the choice of owner or other
That seems totally unrelated to your postgresql error!
IS IT POSSIBLE TO UNINSTALL AND REINSTALL PostgreSQL WITHOUT DAMAGING THE INFORMATION ON SAMBC??? WOULD THIS FIX THE PROBLEM - SINCE IT IS A PROBLEM THAT I HAVE NEVER ENCOUNTERED BEFORE???
You can uninstall and reinstall postgresql. As long as you keep your data directory, which is standard behaviour, the information is being kept. But I DO NOT recommend to do that! And I am quite sure it would NOT fix this problem; which is outside of PostgreSQL. It would be a different matter if the service failed with something like pg_ctl.exe not found; but your PostgreSQL installation seems fine.
Tried to help you online, but you were unavailable... no idea of your time zone, though.Good luck with the repair, and please keep us posted,best wishes,Harald
On 7/11/06, Harald Armin Massa
[EMAIL PROTECTED] wrote:

On 7/11/06, anika evans [EMAIL PROTECTED] wrote:

HELLO!!I STILL NEED HELP -
I removed norton internet security and norton goback - postgre sql still doesn't work!
I need to get this thing running - asap - before my ass is fired!!

On 7/10/06, anika evans [EMAIL PROTECTED] wrote:

Unfortunatly rolling back the update did not work - and adding port 5432 did not work either.

If I cant remove postgre - then what do I do??

also, When I turn on the computer I have 2 accounts on windows ex - the Owner (administrator) account

Re: [GENERAL] pgsql vs mysql


On 6/30/2006 1:07 PM, Merlin Moncure wrote:


* mysql has a few features here and there which are nice...just to
name a few, flush tables with lock, multiple insert, etc


I have no clue what flushing tables with lock might be good for. Are 
applications in MySQuirreL land usually smarter than the DB engine with 
respect to when to checkpoint or not?


The multiple insert stuff is not only non-standard, it also encourages 
the bad practice of using literal values directly in the SQL string 
versus prepared statements with place holders. It is bad practice 
because it introduces SQL injection risks since the responsibility of 
literal value escaping is with the application instead of the driver.


Everything that teaches new developers bad things counts as a 
disadvantage in my book, so -1 on that for MySQL too.



Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL


On 7/10/2006 10:00 PM, Alex Turner wrote:


http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html

5.1


Ah, thanks. So I guess 5.1.5 and 5.1.8 must be considered major feature 
minor/bugfix releases. I still don't understand how people can use 
software in production that has literally zero bugfix upgrade path 
without the risk of incompatibility due to new features. I consider 
every IT manager, who makes that choice, simply overpaid.



Jan



Alex

On 7/10/06, Jan Wieck [EMAIL PROTECTED] wrote:


On 6/30/2006 11:12 AM, Scott Marlowe wrote:
 I agree with Tom, nice notes.  I noted a few minor issues that seem to
 derive from a familiarity with MySQL.  I'll put my corrections below...

 On Fri, 2006-06-30 at 08:17, Jason McManus wrote:
 On Converting from MySQL 5.0.x to PostgreSQL 8.1.x
 --
 Major differences I have noted:
 ---

 MySQL 5.0.x:

 * Easy, built-in and extensive replication support.

 Not sure how extensive it is.  It's basically synchronous single master
 single slave, right?  It is quite easy though.

Last thing I heard was that MySQL still had only statement based
replication and that it doesn't work together with some of the new
enterprise features like triggers and stored procedures. Row level
replication is on their TODO list and this major feature will probably
appear in some minor 5.2.x release.


Jan



 PostgreSQL 8.1.x:
 * Embedded procedures in multiple native languages (stored procedures
and
   functions can be written in native Perl, Python, Tcl, SQL, PL/PgSQL)

 Note that there are a dozen or more other languages as well.  Just FYI.
 Off the top of my head, plPHP, plJ (java there's two different java
 implementations, I think) and plR (R is the open source equivalent of
 the S statistics language)

 * Replication support still rudimentary.

 H.  I think that's an overly simplistic evaluation.  The slony
 replication engine is actually VERY advanced, but the administrative
 tools consist mostly of your brain.  hehe.  That said, once you've
 learned how to drive it, it's quite amazing.  Keep in mind, slony can be
 applied to a living database while it's running, and can run between
 different major versions of postgresql.  That's a pretty advanced
 feature.  Plus, if the replication daemons die (kill -9ed or whatever)
 you can restart replication and slony will come right back where it was
 and catch up.

 Pointers, tips, quick facts and gotchas for other people converting:
 

 * MySQL combines the concepts of 'database' and 'schema' into
one.  PostgreSQL
   differentiates the two.  While the hierarchy in MySQL is
   database.table.field, PostgreSQL is roughly:
database.schema.table.field.
   A schema is a 'logically grouped set of tables but still kept within
a
   particular database.'  This could allow separate applications to be
built
   that still rely upon the same database, but can be kept somewhat
logically
   separated.  The default schema in each database is called 'public',
and is
   the one referred to if no others are specified.  This can be modified
with
   'SET search_path TO ...'.

 This is a VERY good analysis of the difference between the two
 databases.

 * Pg uses a 'template1' pseudo-database that can be tailored to provide
   default objects for new database creation, if you should desire.  It
   obviously also offers a 'template0' database that is read-only and
   offers a barebones database, more equivalent to the empty db created
with
   mysql's CREATE DATABASE statement.

 This isn't quite right.

 template0 is a locked and pure copy of the template database.  It's
 there for break glass in case of emergency use. :)

 template1, when you first initdb, is exactly the same as template0, but
 you can connect to it, and alter it.  Both of these are real
 postgresql databases.  template1 is the database that gets copied by
 default when you do create database.  Note that you can also define a
 different template database when running create database, which lets you
 easily clone any database on your machine.  create database newdb with
 template olddb

 * Pg uses the 'serial' column type instead of AUTO_INCREMENT.  This
allows
   more than one independent sequence to be specified per table (though
the
   utility of this may be of dubious value).  These are closer to
Oracle's
   concept of sequence generators, and they can be manipulated with the
   currval(), nextval(), setval(), and lastval() functions.

 Don't forget 64bit bigserials too.

 * Pg requires its tables and databases be 'vacuumed' regularly to
remove
   completed transaction snapshots and optimize the tables on disk.  It
is
   necessary because the way that PostgreSQL implements true MVCC is by
   writing all temporary transactions to disk and setting a visibility
   flag for the record.  Vacuuming can be performed automatically, and
in

[GENERAL] SQL parsing suggestions?

2006-07-11 Thread Vance Maverick

I have a PostgreSQL schema definition.  I'd like to be able to use it as
the basis for code generation in a software build process --
specifically, I want to generate Java enums corresponding to the table
definitions.  However, it would be inconvenient to have to connect to a
running database during the build.

What approach would people suggest?  I'm open to working in any
language.  Are there PostgreSQL parsing tools out there -- perhaps, the
parser module itself from the database server?  Or something from the
JDBC driver, or the perl or PHP interfaces?  Or does anyone have
experience with other SQL parsers that can handle all the syntax of
PostgreSQL files?

Vance


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

Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL

2006-07-11 Thread Scott Marlowe

On Tue, 2006-07-11 at 10:45, Jan Wieck wrote:
 On 7/10/2006 10:00 PM, Alex Turner wrote:
 
  http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html
  
  5.1
 
 Ah, thanks. So I guess 5.1.5 and 5.1.8 must be considered major feature 
 minor/bugfix releases. I still don't understand how people can use 
 software in production that has literally zero bugfix upgrade path 
 without the risk of incompatibility due to new features. I consider 
 every IT manager, who makes that choice, simply overpaid.

Dear god!  That page made my eyes bleed.

Individual users can choose the method of replication for their
sessions?

There's a mixed method that switches back and forth?

In addition to switching the logging format manually, a slave server
may switch the format automatically.

I'm pretty sure this kind of thing would never get into PostgreSQL. 
It's like reading a map of a minefield drawn in crayon.

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

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

Re: [GENERAL] having indexes when clearing tables


try truncate.  just be aware that truncate is being upgraded in 8.2
for better handling of foreign key isues.

merlin

On 9 Jul 2006 20:35:39 -0700, nuno [EMAIL PROTECTED] wrote:

hi there.

i'm trying to clear data in a table but somehow it takes way too much
time than i once expected (i'm using the formal 'delete from xxx' type
of query.).

this table's got several foreign keys and i suspect that is why it
takes longer than expected.

my question is that
would having indexes increase query performance when clearing tables?
or could anyone suggest me any ways to increase performance of the
query in such cases?

thanks very much.

regards, nuno


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

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

Re: [GENERAL] pgsql vs mysql


On 7/11/06, Jan Wieck [EMAIL PROTECTED] wrote:

On 6/30/2006 1:07 PM, Merlin Moncure wrote:
I have no clue what flushing tables with lock might be good for. Are
applications in MySQuirreL land usually smarter than the DB engine with
respect to when to checkpoint or not?


no, but the ability to flip a database into read only mode with such
an easy command is relatively useful. i very much pg had a read only
mode, btw.


The multiple insert stuff is not only non-standard, it also encourages
the bad practice of using literal values directly in the SQL string
versus prepared statements with place holders. It is bad practice
because it introduces SQL injection risks since the responsibility of
literal value escaping is with the application instead of the driver.


good points, and pg can accomplish similar via insert select union
all, etc., but mysql version of same is better syntax imo, so i guess
i should take it up with the sql standard.  As to preparing
statements, I agree in principle although I don't know if that is a
good argument not to make the non-paramaterized interface more
powerful.


Everything that teaches new developers bad things counts as a
disadvantage in my book, so -1 on that for MySQL too.


no comment. :)  small disclaimer:  I am right now administrating a
relatively large mysql database infrastructure which I inherited.  I
was hired with for the express purpose of converting it to pg.
meanwhile since writing the op I got burned really badly by the mysql
replication where the slave became off synch with master on an
important table, something you might appreciate.

that said, i tried to put fairness in my comparison, many pg/mysql
comparisons ulimately resort to a dismissive mysql diss which does not
play well to the uninformed third party.  so, I made an attempt at
something with some substance.

regards,
merlin

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

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

Re: [GENERAL] pgsql vs mysql

2006-07-11 Thread Scott Marlowe

On Tue, 2006-07-11 at 11:04, Jan Wieck wrote:
 On 6/30/2006 1:07 PM, Merlin Moncure wrote:
 
  * mysql has a few features here and there which are nice...just to
  name a few, flush tables with lock, multiple insert, etc

 The multiple insert stuff is not only non-standard, it also encourages 
 the bad practice of using literal values directly in the SQL string 
 versus prepared statements with place holders.

I thought it was in the SQL 99 standard...  

  It is bad practice 
 because it introduces SQL injection risks since the responsibility of 
 literal value escaping is with the application instead of the driver.

agreed, however.

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

   http://archives.postgresql.org

Re: [GENERAL] pgsql vs mysql


On 7/11/2006 1:08 PM, Scott Marlowe wrote:


On Tue, 2006-07-11 at 11:04, Jan Wieck wrote:

On 6/30/2006 1:07 PM, Merlin Moncure wrote:

 * mysql has a few features here and there which are nice...just to
 name a few, flush tables with lock, multiple insert, etc


The multiple insert stuff is not only non-standard, it also encourages 
the bad practice of using literal values directly in the SQL string 
versus prepared statements with place holders.


I thought it was in the SQL 99 standard...  


The SQL bible doesn't say SQL99, it says it is a DB2 specific feature.


Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

Re: [GENERAL] [pgadmin-support] Emergency - postgre is not working


On 7/11/06, Harald Armin Massa [EMAIL PROTECTED] wrote:

Propblem gets clearer. I had similiar symptoms before; so my rough guess is:
your postgres user did loose a privilege.

Technical background:
the postgres-account is a low privileged user, which logs on automatically
when the service starts up. So he needs the privilege log on as service

And I think that your postgres account loosed that privilege.

So I recommend to try the following steps:

a) startup the Editor of user accounts (within control panel)
b) change the password of the postgres user account to something you know
c) restart services.msc, and open the Properties of the Postgres Services
d) go to the logon page, and enter the new password you just gave to this
account (step b)
e) try to start the service again from the service control panel

With entering the logon information again to the logon-tab of the service
properties dialog, you are re-giving that privilege.


I'm pretty sure that simply going into properties of the service and
clicking ok will (re)grant the log on as service right.

merlin

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

  http://archives.postgresql.org

Re: [GENERAL] SQL parsing suggestions?

2006-07-11 Thread John DeSoi



On Jul 11, 2006, at 11:49 AM, Vance Maverick wrote:

I have a PostgreSQL schema definition.  I'd like to be able to use  
it as

the basis for code generation in a software build process --
specifically, I want to generate Java enums corresponding to the table
definitions.  However, it would be inconvenient to have to connect  
to a

running database during the build.

What approach would people suggest?



I would suggest connect to a running database during the build :)

Maybe if you don't want to connect for every build, you could create  
a procedure that runs as part of any schema update to the database.  
This procedure would generate a simple, easily-to-parse file of the  
schema information you need for your build. Building this file from  
the information schema when you are connected to the database should  
be an easy task in comparison to creating a parser.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


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

Re: [GENERAL] pgsql vs mysql

2006-07-11 Thread Guido Neitzer


On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:


As to preparing
statements, I agree in principle although I don't know if that is a
good argument not to make the non-paramaterized interface more
powerful.


It is not, as prepared statements have the problem that they are only  
optimized once and very generically and without actual knowledge of  
the parameter content, this is just useless.


I had the problem a few months ago, where my app server plugin and  
the jdbc driver used prepared statements for selecting stuff from the  
database. Most of the time, indexes weren't used at all, so  
PostgreSQL performance was the worst I've ever seen in this environment.


There are fixes for that, but it should be made easier ...

cug

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

[GENERAL] troubleshooting 8.1.2

2006-07-11 Thread Ed L.

We have 4 8.1.2 cluster running on an HP-UX 11.23 Itanium, repeatedly
dying with the following log message:

2006-07-11 12:52:27 EDT [21582]LOG:  received fast shutdown request
2006-07-11 12:52:27 EDT [21591]LOG:  shutting down
2006-07-11 12:52:27 EDT [21591]LOG:  database system is shut down
2006-07-11 12:52:27 EDT [21584]LOG:  logger shutting down

We can't figure out why it is shutting down.  Nobody here is sending
the signal.  We don't have any cron jobs doing that sort of thing.

We've also seen out of memory errors when this first started
happening, though glance had not shown GBL_MEM_UTIL above 90% (with
OS buffer cache max/min percents at 10%/3%).  The box has 64gb of
RAM, so that would seem to mean there was ~6GB of RAM available
when it got the out of memory errors.

Just in case, we shutdown several clusters, and restarted them, and
now even with plentiful memory, they're dying with the same message.

Any ideas?

Ed

---(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] Notes on converting from MySQL 5.0.x to PostgreSQL


On 7/11/2006 11:57 AM, Scott Marlowe wrote:


On Tue, 2006-07-11 at 10:45, Jan Wieck wrote:

On 7/10/2006 10:00 PM, Alex Turner wrote:

 http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html
 
 5.1


Ah, thanks. So I guess 5.1.5 and 5.1.8 must be considered major feature 
minor/bugfix releases. I still don't understand how people can use 
software in production that has literally zero bugfix upgrade path 
without the risk of incompatibility due to new features. I consider 
every IT manager, who makes that choice, simply overpaid.


Dear god!  That page made my eyes bleed.

Individual users can choose the method of replication for their
sessions?

There's a mixed method that switches back and forth?


It is totally unclear from that page what would make the server decide 
when to pick one or the other method. It seems to me that this is mainly 
an optimization for many single inserts in order to get a smaller 
binlog. Note that according to this page


http://dev.mysql.com/doc/internals/en/replication-prepared-statements.html

the master currently substitutes the parameters as literals into the 
query for prepared statements.


What also is totally unclear, maybe someone with more MySQL experience 
can answer this question, is if the binary format actually does solve 
the problems discussed. Namely timestamps and also autoincrement. What 
exactly happens if an insert doesn't provide a value for an autoinc or 
timestamp column? Is the server chosen value placed into the binlog when 
using row format or not?



Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

Re: [GENERAL] pgsql vs mysql


On 7/11/2006 1:36 PM, Merlin Moncure wrote:


that said, i tried to put fairness in my comparison, many pg/mysql
comparisons ulimately resort to a dismissive mysql diss which does not
play well to the uninformed third party.  so, I made an attempt at
something with some substance.


Totally understood. The life vest that is not worn because it is too 
complicated to put on doesn't save any lives. Meaning the simplicity of 
setting up MySQL replication means it is used more often. One just has 
to keep in mind to rebuild the slaves from time to time because they get 
out of sync without any visible failure. Slony-I on the other hand is a 
steeper learning curve, and although it could serve as a much more 
reliable backup solution, it isn't used nearly as often as it should.



Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

---(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] encoding bug or feature?

marcelo Cortez [EMAIL PROTECTED] writes:
 testutf8=# \set
 ...
 ENCODING = 'UTF8'
 HISTSIZE = '500'
 testutf8=# select upper('ñ');
 ERROR:  invalid UTF-8 byte sequence detected near byte
 0xf1
 testutf8=#

You're telling the system that your client encoding is utf8, but
it looks from here like you're using some LatinN encoding.
Try \encoding latin1 or whatever it is your keyboard is generating.

regards, tom lane

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

Re: [GENERAL] pgsql vs mysql

2006-07-11 Thread Joshua D. Drake


 The multiple insert stuff is not only non-standard, it also encourages
 the bad practice of using literal values directly in the SQL string
 versus prepared statements with place holders. It is bad practice
 because it introduces SQL injection risks since the responsibility of
 literal value escaping is with the application instead of the driver.

It is also something that users are clammoring for (and my customers). To
the point that I have customers using unions to emulate the behavior. Why?
Because it is really, really fast.

Joshua D. Drake


-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.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

[GENERAL] doesn't recognize !=- (not equal to a negative value)

2006-07-11 Thread Paul Tilles


Version postgres 7.4.7:

Following sql

UPDATE tablename SET value = 0.0 where value!=-9.4;

results in the error message

ERROR:  operator does not exist: smallint !=- integer
HINT:  No operator matches the given name and argument type(s). You may 
need to add explicit type casts.


Seems that postgres has a problem parsing a not equal negative value.

Anybody know if this is fixed in later versions?

Paul Tilles


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

  http://archives.postgresql.org

Re: [GENERAL] SQL parsing suggestions?


On 7/11/06, Vance Maverick [EMAIL PROTECTED] wrote:

I have a PostgreSQL schema definition.  I'd like to be able to use it as
the basis for code generation in a software build process --
specifically, I want to generate Java enums corresponding to the table
definitions.  However, it would be inconvenient to have to connect to a
running database during the build.

What approach would people suggest?  I'm open to working in any
language.  Are there PostgreSQL parsing tools out there -- perhaps, the
parser module itself from the database server?  Or something from the
JDBC driver, or the perl or PHP interfaces?  Or does anyone have
experience with other SQL parsers that can handle all the syntax of
PostgreSQL files?


I would load the schema definition into a postgresql server and then
query information_shcema  to introduce formatting :)

In a previous project, I was able go generate COBOL fd files quite
easily from postgresql tables/'views via simple querying off of
information schema.

merlin

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

Re: [GENERAL] troubleshooting 8.1.2

2006-07-11 Thread Ed L.

On Tuesday July 11 2006 1:17 pm, Tom Lane wrote:
 Ed L. [EMAIL PROTECTED] writes:
  We have 4 8.1.2 cluster running on an HP-UX 11.23 Itanium,
  repeatedly dying with the following log message:
 
  2006-07-11 12:52:27 EDT [21582]LOG:  received fast
  shutdown request

 *Something* is sending SIGINT to the postmaster --- it's
 simply not possible to reach that elog call any other way.

 How are you launching the postmaster?  If from a terminal
 window, are you sure it's entirely disconnected from the
 terminal's process group? If not, typing control-C in that
 window could SIGINT the postmaster.

We use a shell function to start the postmaster:

dbstart() {
pg_ctl start -D $PGDATA -m smart -o -i -p $PGPORT -p postmaster
}

We are wondering if our swap space was too small, and when the
swap reservation failed, the OS was sending SIGINT??

Ed

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

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

Re: [GENERAL] Help making a plpgsql function?


On 7/5/2006 3:51 PM, Bjørn T Johansen wrote:


Yes, but I need to return n fields from one table and n fiels from another, and 
n fields from yet another
table, etc... and return this as some kind of record... How do I to this?


I wonder why your problem can't be solved by a simple join.


Jan




BTJ

On Wed, 5 Jul 2006 19:13:39 +0200
Dany De Bontridder [EMAIL PROTECTED] wrote:


On Wednesday 05 July 2006 16:46, Bjørn T Johansen wrote:
 I need to make a funtion that take one parameter and then returns a
 record with x number of fields, collected from x no. of tables, i.e. I
 need to run several sql statemtents to collect all the needed values from x
 no. of fields and insert it into a record and return the record at the
 end...
From http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html

Example for function having return type set of record

create function testfunc(int) returns record as '
declare
output record;
begin
for output in select * from table1 where col1$1 loop
return next output;
end loop;
return;
end;
' language plpgsql

executing through sql as:

select * from testfunc(6) as (col1 int, col2 float, col3 char(20));


Regards,

D.

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


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



--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

Re: [GENERAL] encoding bug or feature?

2006-07-11 Thread marcelo Cortez

Tom , folks

 I've  recreated the database with --locale=es_AR
keyword
 and all works fine

 thanks a lot.

 best
  MDC

 --- Tom Lane [EMAIL PROTECTED] escribió:

 marcelo Cortez [EMAIL PROTECTED] writes:
  testutf8=# \set
  ...
  ENCODING = 'UTF8'
  HISTSIZE = '500'
  testutf8=# select upper('ñ');
  ERROR:  invalid UTF-8 byte sequence detected near
 byte
  0xf1
  testutf8=#
 
 You're telling the system that your client encoding
 is utf8, but
 it looks from here like you're using some LatinN
 encoding.
 Try \encoding latin1 or whatever it is your
 keyboard is generating.
 
   regards, tom lane
 






___ 
1GB gratis, Antivirus y Antispam 
Correo Yahoo!, el mejor correo web del mundo 
http://correo.yahoo.com.ar 


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


On 7/11/06, Guido Neitzer [EMAIL PROTECTED] wrote:

On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:

 As to preparing
 statements, I agree in principle although I don't know if that is a
 good argument not to make the non-paramaterized interface more
 powerful.

It is not, as prepared statements have the problem that they are only
optimized once and very generically and without actual knowledge of
the parameter content, this is just useless.



there is some confusion (not necessarily by you) between paramaterized
and prepared statements.  parameterized is when the query parameters
are separate from the query string itself, you can do this with or
without preparing them.  parameterizing statements is  basically
always a good thing...you get something for nothing.

preparing can work great or not depending on what you are trying to
do.  If they work then can cut as much of 50% of the query time and if
they dont work...well you know what happens.  I can vouch for this,
for example I like to parameterize the limit clause but this can
confuse the planner.  Still, overall, when used carefully and
properly, they can supercharge your server.

merlin

---(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] troubleshooting 8.1.2

Ed L. [EMAIL PROTECTED] writes:
 We have 4 8.1.2 cluster running on an HP-UX 11.23 Itanium, repeatedly
 dying with the following log message:

 2006-07-11 12:52:27 EDT [21582]LOG:  received fast shutdown request

*Something* is sending SIGINT to the postmaster --- it's simply not
possible to reach that elog call any other way.

How are you launching the postmaster?  If from a terminal window, are
you sure it's entirely disconnected from the terminal's process group?
If not, typing control-C in that window could SIGINT the postmaster.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] pgsql vs mysql

2006-07-11 Thread Alvaro Herrera

Guido Neitzer wrote:
 On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:
 
 As to preparing
 statements, I agree in principle although I don't know if that is a
 good argument not to make the non-paramaterized interface more
 powerful.
 
 It is not, as prepared statements have the problem that they are only  
 optimized once and very generically and without actual knowledge of  
 the parameter content, this is just useless.
 
 I had the problem a few months ago, where my app server plugin and  
 the jdbc driver used prepared statements for selecting stuff from the  
 database. Most of the time, indexes weren't used at all, so  
 PostgreSQL performance was the worst I've ever seen in this environment.

I'm pretty excited about this idea of yours on how to fix this problem.
Does it involve the histogram at all?

Maybe we could check the MCVs, and store those for which the plan would
be A (say indexscan) and those for which it would be B (say bitmap
indexscan), etc; so we'd save more than one plan and choose at execution
time depending on the actual parameters.  For all values not on the
MCV set, use the same plan as the least common of the MCVs.

But of course, I know nothing about the optimizer so I'm not sure if
this makes any sense at all.

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

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

   http://archives.postgresql.org

Re: [GENERAL] doesn't recognize !=- (not equal to a negative value)

Paul Tilles [EMAIL PROTECTED] writes:
 UPDATE tablename SET value = 0.0 where value!=-9.4;
 ERROR:  operator does not exist: smallint !=- integer
 HINT:  No operator matches the given name and argument type(s). You may 
 need to add explicit type casts.

This is not a bug, this is a feature.

Put a space between, or else use the SQL-standard spelling of not-equals,
ie 

UPDATE tablename SET value = 0.0 where value!= -9.4;
UPDATE tablename SET value = 0.0 where value-9.4;

regards, tom lane

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

Re: [GENERAL] pgsql vs mysql

Jan Wieck [EMAIL PROTECTED] writes:
 On 7/11/2006 1:08 PM, Scott Marlowe wrote:
 I thought it was in the SQL 99 standard...  

 The SQL bible doesn't say SQL99, it says it is a DB2 specific feature.

If you're speaking of INSERT INTO foo VALUES (a, row), (another, row), ...
that's in SQL92.  See 7.2 table value constructor:

 table value constructor ::=
  VALUES table value constructor list

 table value constructor list ::=
  row value constructor [ { comma row value constructor }... ]

It's really pretty lame that we still don't have any support at all for
this :-(.  Allowing it everywhere the spec says table value constructor
should be allowed might be nontrivial ... but maybe we should just fix
the INSERT ... VALUES case for now.

regards, tom lane

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

Re: [GENERAL] pgsql vs mysql

2006-07-11 Thread Stefan Kaltenbrunner

Jan Wieck wrote:
 On 7/11/2006 1:08 PM, Scott Marlowe wrote:
 
 On Tue, 2006-07-11 at 11:04, Jan Wieck wrote:
 On 6/30/2006 1:07 PM, Merlin Moncure wrote:

  * mysql has a few features here and there which are nice...just to
  name a few, flush tables with lock, multiple insert, etc

 The multiple insert stuff is not only non-standard, it also
 encourages the bad practice of using literal values directly in the
 SQL string versus prepared statements with place holders.

 I thought it was in the SQL 99 standard...  
 
 The SQL bible doesn't say SQL99, it says it is a DB2 specific feature.

hmm:

http://troels.arvin.dk/db/rdbms/#insert

says otherwise - or are we talking a different multiple insert ?


Stefan

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

Re: [GENERAL] doesn't recognize !=- (not equal to a negative

2006-07-11 Thread Scott Marlowe

On Tue, 2006-07-11 at 12:11, Paul Tilles wrote:
 Version postgres 7.4.7:
 
 Following sql
 
 UPDATE tablename SET value = 0.0 where value!=-9.4;
 
 results in the error message
 
 ERROR:  operator does not exist: smallint !=- integer
 HINT:  No operator matches the given name and argument type(s). You may 
 need to add explicit type casts.
 
 Seems that postgres has a problem parsing a not equal negative value.
 
 Anybody know if this is fixed in later versions?

U.  technically, it's not broken.

SQL spec says not equal is specified by:



not 

!=

OTOH, if you put a space in there, it'd work.

UPDATE tablename SET value = 0.0 where value != -9.4;

should work.

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

Re: [GENERAL] customizing pg_dump together with copy.c's DoCopy function

2006-07-11 Thread [EMAIL PROTECTED]

  Is it possible to compile-link together frontend pg_dump code with
  backend code from copy.c?

 No.  Why do you think you need to modify pg_dump at all?


pg_dump and pg_restore provide important advantages for upgrading a
customer's database on site:

They are fast. I want to minimize downtime.
They allow compression. I often will have relatively little free disk
space to work with.
My concept is customized dump, drop database, create new schema
database, customized restore.

My upgrade requires many schema and data content changes. I've tried
using standard SQL statements in perl scripts to do all of it, but even
with no indexes on inserts, later creating indexes for the lookup work,
and every other optimization I know of, a 100gb database requires
several days to turn our old database into a new one. I was hoping that
I could modify the speedy pg_dump/pg_restore utilities to make these
changes on the fly. It gets tricky because I have to restore some of
the data to different tables having varying schema and also change the
table linking. But this is all doable as long as I can massage the
SQL statements and data both when it goes into the dump file and when
it is getting restored back out.

Or am I trying to do the impossible?
-Lynn


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

[GENERAL] Including C/C++ header files containing #defines using EXEC SQL INCLUDE

2006-07-11 Thread Mitch.Logue

Title: Including C/C++ header files containing #defines using EXEC SQL INCLUDE







Here is the problem I am trying to solve:


I have Header files (C and C++) that check to ensure you don't load them twice by doing the following:


#ifndef MY_HDR_FILE

#define MY_HDR_FILE

Blah

Blah

Blah

#endif MY_HDR_FILE


I would like to be able to include these using EXEC SQL INCLUDE inside a EXEC SQL BEGIN/END block, but as I have seen in my readings from the Postgres groups and Google searches it seems that these types of #defines are nor parsed by the Postgres pre-processor and only handled by the C/C++ Pre-Processor (but I'd also like to point out that the only references to this issue was from a post back in 1999, so since then, there could have been changes that I'm just not aware of how to use -- he says hopefully). But, because I'm porting from Oracle, all my code does this legally in Oracle (Pro*C), I'm not saying that makes it right, but I am asking if there is a way of doing this using Postgres?

I'm building these applications using ecpg (PostgreSQL 8.1.3) 4.1.1 on a Linux system:

Linux version 2.4.21-37.0.1.ELsmp ([EMAIL PROTECTED]) (gcc version 3.2.3 20030502 (Red Hat Linux 3.2.3-54)) #1 SMP Wed Jan 11 18:44:17 EST 2006

Any assistance in this would be greatly appreciated, otherwise I am going to have to re-write a great many header files, and then also modify the source code that calls it so it only calls the C/C++ header portion because I can't keep each separate program from re-loading the header files without the #define's.

Thank you in advance

[GENERAL] PostgreSQL Server Crash using plPHP or PL/Perl

2006-07-11 Thread Carl M. Nasal II

We are writing a multi-master replication process for our Electronic 
Medical Records product.  We have written triggers in plPHP and then in 
PL/Perl to keep an audit trail of the changes as well as flags so the data 
can be replicated.  We started with plPHP, but then server started 
crashing, which reset all connections to the database (requiring our 
application to be restarted).  We then tried to rewrite the code using 
PL/Perl, but the same problem has occurred.  The code for the triggers are 
available at:


http://medical.bmaenterprises.com/audit.plphp
http://medical.bmaenterprises.com/audit.plperl

We create the triggers by running the follow SQL statement for each table:

CREATE TRIGGER config_audit AFTER INSERT OR UPDATE OR DELETE ON config FOR 
EACH ROW EXECUTE PROCEDURE audit();


Any ideas of what is causing the server to crash will be helpful.

Below are the lines from the PostgreSQL serverlog file when the crash occurs:

LOG:  server process (PID 29153) exited with exit code 255
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server process exited
HINT:  In a moment you should be able to reconnect to the database and 
repeat your command.

LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2006-07-11 16:01:32 EDT
LOG:  checkpoint record is at 1/F413F26C
LOG:  redo record is at 1/F413F26C; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 7628670; next OID: 693120
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system was not properly shut down; automatic recovery in 
progress

LOG:  redo starts at 1/F413F2B0
LOG:  record with zero length at 1/F4186D3C
LOG:  redo done at 1/F4186D14
LOG:  database system is ready
LOG:  transaction ID wrap limit is 2147484146, limited by database postgres


Thank you,
Carl M. Nasal II
BMA Enterprises, Inc.

---(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] doesn't recognize !=- (not equal to a negative value)

2006-07-11 Thread Eric B. Ridge


On Jul 11, 2006, at 1:11 PM, Paul Tilles wrote:

ERROR:  operator does not exist: smallint !=- integer
HINT:  No operator matches the given name and argument type(s). You  
may need to add explicit type casts.


I'm pretty sure the SQL-standard spelling of not equals is .   
Postgres supporting != is just a nicety.  In fact, the  
documentation for comparison operators states that The != operator  
is converted to  in the parser stage..  http://www.postgresql.org/ 
docs/8.1/static/functions-comparison.html


Alternatively, you can put a space before the minus sign:

UPDATE tablename SET value = 0.0 where value!= -9.4;

I think the reason for what seems like a mis-parsing is due to  
Postgres' extensible operator system.  Postgres can't disambiguate  
what you mean by !=- because those three characters are also valid  
in custom operators.  See http://www.postgresql.org/docs/8.1/static/ 
sql-createoperator.html for the complete list of valid characters.


eric

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

Re: [GENERAL] troubleshooting 8.1.2

2006-07-11 Thread Douglas McNaught

Ed L. [EMAIL PROTECTED] writes:

 We are wondering if our swap space was too small, and when the
 swap reservation failed, the OS was sending SIGINT??

I've never heard of an OS sending that particular signal for a memory
shortage.  'strace' may be your friend here.

-Doug

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

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

Re: [GENERAL] doesn't recognize !=- (not equal to a negative value)

2006-07-11 Thread Tim Hart

From a brief and similar session (below), perhaps the best solution is to
simply insert a space between the '=' and the '-'??


Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

Warning: Console code page (437) differs from Windows code page (1252)
 8-bit characters may not work correctly. See psql reference
 page Notes for Windows users for details.

TJHart=# select 0.0 != -9.4;
 ?column?
--
 t
(1 row)

TJHart=# select 0.0 !=-9.4;
ERROR:  operator does not exist: numeric !=- numeric
HINT:  No operator matches the given name and argument type(s). You may need
to add explicit type casts.
TJHart=#

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Paul Tilles
Sent: Tuesday, July 11, 2006 12:11 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] doesn't recognize !=- (not equal to a negative value)

Version postgres 7.4.7:

Following sql

UPDATE tablename SET value = 0.0 where value!=-9.4;

results in the error message

ERROR:  operator does not exist: smallint !=- integer
HINT:  No operator matches the given name and argument type(s). You may 
need to add explicit type casts.

Seems that postgres has a problem parsing a not equal negative value.

Anybody know if this is fixed in later versions?

Paul Tilles
 

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

   http://archives.postgresql.org



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

   http://archives.postgresql.org

Re: [GENERAL] doesn't recognize !=- (not equal to a negative value)

2006-07-11 Thread Paul Tilles


Yes.  That works.  I think that the parser should work properly either way.

Paul

Bruce Momjian wrote:

Paul Tilles wrote:
  

Version postgres 7.4.7:

Following sql

UPDATE tablename SET value = 0.0 where value!=-9.4;

results in the error message

ERROR:  operator does not exist: smallint !=- integer
HINT:  No operator matches the given name and argument type(s). You may 
need to add explicit type casts.



Have you tried?

value != -9.4

---

  

Seems that postgres has a problem parsing a not equal negative value.

Anybody know if this is fixed in later versions?

Paul Tilles
 


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

   http://archives.postgresql.org



  


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

Re: [GENERAL] troubleshooting 8.1.2

Ed L. [EMAIL PROTECTED] writes:
 We are wondering if our swap space was too small, and when the
 swap reservation failed, the OS was sending SIGINT??

You'd have to check your OS documentation ...  I thought HPUX would
just return ENOMEM to brk() for such cases.  It doesn't do memory
overcommit does it?

regards, tom lane

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

Re: [GENERAL] pgsql vs mysql

2006-07-11 Thread Ron Johnson

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Joshua D. Drake wrote:
 The multiple insert stuff is not only non-standard, it also encourages
 the bad practice of using literal values directly in the SQL string
 versus prepared statements with place holders. It is bad practice
 because it introduces SQL injection risks since the responsibility of
 literal value escaping is with the application instead of the driver.
 
 It is also something that users are clammoring for (and my customers). To
 the point that I have customers using unions to emulate the behavior. Why?
 Because it is really, really fast.

When inserting multiple rows in the same INSERT statement, how do
you tell which row fails on a constraint or datatype-mismatch violation?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is common sense really valid?
For example, it is common sense to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that common sense is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEtAkFS9HxQb37XmcRAvfaAJ4viPqLt8g1aHR6H9l7lEjM13EWcgCgulAx
rPuCH7OSDeb7RuKBuywm5k4=
=RRQx
-END PGP SIGNATURE-

---(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] doesn't recognize !=- (not equal to a negative value)

2006-07-11 Thread Oisin Glynn


Paul Tilles wrote:

Version postgres 7.4.7:

Following sql

UPDATE tablename SET value = 0.0 where value!=-9.4;

results in the error message

ERROR:  operator does not exist: smallint !=- integer
HINT:  No operator matches the given name and argument type(s). You 
may need to add explicit type casts.


Seems that postgres has a problem parsing a not equal negative value.

Anybody know if this is fixed in later versions?

Paul Tilles


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

  http://archives.postgresql.org
Just tested on 8.1.1  I was getting the same error but if i put a space 
between the = and the - it works!


Oisin


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

Re: [GENERAL] pgsql vs mysql

2006-07-11 Thread Guido Neitzer


On 11.07.2006, at 21:11 Uhr, Alvaro Herrera wrote:


I had the problem a few months ago, where my app server plugin and
the jdbc driver used prepared statements for selecting stuff from the
database. Most of the time, indexes weren't used at all, so
PostgreSQL performance was the worst I've ever seen in this  
environment.


I'm pretty excited about this idea of yours on how to fix this  
problem.

Does it involve the histogram at all?


There is no idea. It's only not using prepared statements right now.  
You can force this in the jdbc driver (using protocolVersion=2 in the  
connection url) or you can send different stuff from the application.  
There is nothing where the DB itself does anything.


Nothing special.

cug


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

  http://archives.postgresql.org

Re: [GENERAL] doesn't recognize !=- (not equal to a negative

2006-07-11 Thread Bruce Momjian

Paul Tilles wrote:
 Version postgres 7.4.7:
 
 Following sql
 
 UPDATE tablename SET value = 0.0 where value!=-9.4;
 
 results in the error message
 
 ERROR:  operator does not exist: smallint !=- integer
 HINT:  No operator matches the given name and argument type(s). You may 
 need to add explicit type casts.

Have you tried?

value != -9.4

---

 
 Seems that postgres has a problem parsing a not equal negative value.
 
 Anybody know if this is fixed in later versions?
 
 Paul Tilles
  
 
 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?
 
http://archives.postgresql.org

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] Need help with quote escaping in exim for postgresql

  SQL_ASCII may also be an option (assign no special meaning to
  characters at all), but I'm less sure of that. Can email address
  contain multibyte characters? I didn't think so...
 
 E-Mail addreses themselves can't, but the comment field of an
 address can.

The comment field itself, in RFC2822 addresses is ascii. However there is
a hack, by which this ascii string may be interpretted as representing a
string in another encoding. I don't remember the number of the RFC describing
how this works, but it shouldn't be hard to find.

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

Re: [GENERAL] PostgreSQL Server Crash using plPHP or PL/Perl

2006-07-11 Thread Agent M

Are you certain that it is the trigger that is crashing the process? If  
that is true, then there may be a bug in plperl.


To debug, you could use gdb, but try this first:
Use the strict pragma. To do this in plperl (instead of plperlu), use:
BEGIN { strict-import(); }
	or set strict mode to on in postgresql.conf [I don't understand why  
this isn't the default.]
	You will need to declare all your variables using my $var. [You are  
already half-way there because you declare a lot of empty strings.]


You already pepper your code with elog(NOTICE,) so you can tell us  
how far the code gets right? You can use more elogs to hone in on the  
line that crashes.


Are you aware that your code will be very costly to execute?

On Jul 11, 2006, at 5:43 PM, Carl M. Nasal II wrote:

We are writing a multi-master replication process for our Electronic  
Medical Records product.  We have written triggers in plPHP and then  
in PL/Perl to keep an audit trail of the changes as well as flags so  
the data can be replicated.  We started with plPHP, but then server  
started crashing, which reset all connections to the database  
(requiring our application to be restarted).  We then tried to rewrite  
the code using PL/Perl, but the same problem has occurred.  The code  
for the triggers are available at:


http://medical.bmaenterprises.com/audit.plphp
http://medical.bmaenterprises.com/audit.plperl

We create the triggers by running the follow SQL statement for each  
table:


CREATE TRIGGER config_audit AFTER INSERT OR UPDATE OR DELETE ON config  
FOR EACH ROW EXECUTE PROCEDURE audit();


Any ideas of what is causing the server to crash will be helpful.

Below are the lines from the PostgreSQL serverlog file when the crash  
occurs:
--- 
-

LOG:  server process (PID 29153) exited with exit code 255
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server  
process
DETAIL:  The postmaster has commanded this server process to roll back  
the current transaction and exit, because another server process  
exited
HINT:  In a moment you should be able to reconnect to the database and  
repeat your command.

LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2006-07-11 16:01:32 EDT
LOG:  checkpoint record is at 1/F413F26C
LOG:  redo record is at 1/F413F26C; undo record is at 0/0; shutdown  
FALSE

LOG:  next transaction ID: 7628670; next OID: 693120
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system was not properly shut down; automatic recovery  
in progress

LOG:  redo starts at 1/F413F2B0
LOG:  record with zero length at 1/F4186D3C
LOG:  redo done at 1/F4186D14
LOG:  database system is ready
LOG:  transaction ID wrap limit is 2147484146, limited by database  
postgres



Thank you,
Carl M. Nasal II
BMA Enterprises, Inc.

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


¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
[EMAIL PROTECTED]
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬


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

Re: [GENERAL] Passwords problems

2006-07-11 Thread John DeSoi



On Jul 11, 2006, at 7:43 AM, zordax wrote:

I have funny problem, which i don't understand. I'm adding new  
users to PostgreSQL with some password (for example : aa) and  
i can't log in any database. But after that i change password to  
bb and i can log in. Some passwords are good, and some are  
bad. And it depends on user too (for one user aa is good, and  
for another it is not).


What tool are you using to create users/passwords? If you are using a  
GUI tool, have you tried to replicate the problem using psql?



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


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

  http://archives.postgresql.org

Re: [GENERAL] US Telephone Number Type

On Mon, Jul 10, 2006 at 20:05:13 -0400,
  Chris Browne [EMAIL PROTECTED] wrote:
 worse, over time.  Fortunately LD rates have been tending to fall...

Unless you call a country where the local phone company is charging userous
rates andmay be giving kickbacks to people who can get people to call them
thinking that they will be charged their normal long distance rate because
the number looks like a normal (not international) phone number.

---(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] Need help with quote escaping in exim for postgresql

2006-07-11 Thread Steve Atkins



On Jul 11, 2006, at 6:29 PM, Bruno Wolff III wrote:


SQL_ASCII may also be an option (assign no special meaning to
characters at all), but I'm less sure of that. Can email address
contain multibyte characters? I didn't think so...


E-Mail addreses themselves can't, but the comment field of an
address can.


The comment field itself, in RFC2822 addresses is ascii. However  
there is
a hack, by which this ascii string may be interpretted as  
representing a
string in another encoding. I don't remember the number of the RFC  
describing

how this works, but it shouldn't be hard to find.


RFC 1522. Whether you'd want to open that can of worms
by decoding headers in that format to some other character
set for email is a good question (especially as a lot of spam
has headers that end up containing illegal characters if
you do that).

If you were to do that you'd probably have to deal with
i18n domain name encoding too, which is even more hideous.

Fortunately all this stuff is MUA-side, not MTA-side, so exim
should ignore it. SQL_ASCII all the way.

Cheers,
  Steve


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

Re: [GENERAL] Dynamic table with variable number of columns

On Tue, Jul 11, 2006 at 06:05:18 -0700,
  [EMAIL PROTECTED] wrote:
 Hello,
 I'm a pgsql novice and here is what I'm trying to do:
 1.I need to create a dynamic table with the column names fetched
 from the database using a select statement from some other table.  Is
 it possible?  Could you point me to a simple example on how to do it?
 2.   I would like to compare the list of coulmn names which are values
 fetched from some table with the column names of the existing table.
 If one of the names doesn't exist as a column name of my table, I'd
 like to dynamically alter the table and add a coulmn with the name just
 fetched from the DB.
 Your help is greatly appreciated.
 Thanks
 NK

Information on the column names of tables in the database are available
from the information schema and the catlog tables. You can find more about this
in the documentation:
http://www.postgresql.org/docs/8.1/static/information-schema.html
http://www.postgresql.org/docs/8.1/static/catalogs.html

You might get better help by describing the actual problem you are trying to
solve rather than asking for help with a particular approach to solving that
problem. The approach you are trying seems to be seriously broken and it
would probably be a good idea to consider other approaches.

---(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] doesn't recognize !=- (not equal to a negative value)

On Tue, Jul 11, 2006 at 13:11:16 -0400,
  Paul Tilles [EMAIL PROTECTED] wrote:
 Version postgres 7.4.7:
 
 Following sql
 
 UPDATE tablename SET value = 0.0 where value!=-9.4;
 
 results in the error message
 
 ERROR:  operator does not exist: smallint !=- integer
 HINT:  No operator matches the given name and argument type(s). You may 
 need to add explicit type casts.
 
 Seems that postgres has a problem parsing a not equal negative value.
 
 Anybody know if this is fixed in later versions?

I don't think this is a bug. Postgres allows for user defined operators and
!=- looks like one operator rather than two. Putting a space between = and -
will fix the problem. For example:
bruno= select 1 != -1;
 ?column?
--
 t
(1 row)

bruno= select 1 !=-1;
ERROR:  operator does not exist: integer !=- integer
HINT:  No operator matches the given name and argument type(s). You may need to 
add explicit type casts.

---(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] having indexes when clearing tables