Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

[Stephen Frost]

Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote:
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > > chiru r  writes:
> > > > We are looking  for User profiles in ope source PostgreSQL.
> > > > For example, If a  user password failed n+ times while login ,the user
> > > > access has to be blocked few seconds.
> > > > Please let us know, is there any plan to implement user profiles in 
> > > > feature
> > > > releases?.
> > > 
> > > Not particularly.  You can do that sort of thing already via PAM,
> > > for example.
> > 
> > Ugh, hardly and it's hokey and a huge pain to do, and only works on
> > platforms that have PAM.
> > 
> > Better is to use an external authentication system (Kerberos, for
> > example) which can deal with this, but I do think this is also something
> > we should be considering for core, especially now that we've got a
> > reasonable password-based authentication method with SCRAM.
> 
> Does LDAP do this too?

Active Directory does this, with Kerberos as the authentication
mechanism.  Straight LDAP might also support it, but I wouldn't
recommend it because it's really insecure as the PG server will see the
user's password in the cleartext (and it may be sent in cleartext across
the network too unless careful steps are taken to make sure that the
client only ever connects over SSL to a known trusted and verified
server).

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

[chiru r]

Yes, LDAP will do. However we need to sync the user accounts and  groups
between AD and PG servers.and then AD profiles will apply to PG user
accounts for authentication.

It is good if we have user profiles in core PostgreSQL database system. So
it will add more security.

Thanks,
Chiranjeevi

On Tue, Sep 19, 2017 at 3:09 PM, Bruce Momjian  wrote:

> On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote:
> > Tom,
> >
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > > chiru r  writes:
> > > > We are looking  for User profiles in ope source PostgreSQL.
> > > > For example, If a  user password failed n+ times while login ,the
> user
> > > > access has to be blocked few seconds.
> > > > Please let us know, is there any plan to implement user profiles in
> feature
> > > > releases?.
> > >
> > > Not particularly.  You can do that sort of thing already via PAM,
> > > for example.
> >
> > Ugh, hardly and it's hokey and a huge pain to do, and only works on
> > platforms that have PAM.
> >
> > Better is to use an external authentication system (Kerberos, for
> > example) which can deal with this, but I do think this is also something
> > we should be considering for core, especially now that we've got a
> > reasonable password-based authentication method with SCRAM.
>
> Does LDAP do this too?
>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I.  As I am, so you will be. +
> +  Ancient Roman grave inscription +
>

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

[Bruce Momjian]

On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote:
> Tom,
> 
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > chiru r  writes:
> > > We are looking  for User profiles in ope source PostgreSQL.
> > > For example, If a  user password failed n+ times while login ,the user
> > > access has to be blocked few seconds.
> > > Please let us know, is there any plan to implement user profiles in 
> > > feature
> > > releases?.
> > 
> > Not particularly.  You can do that sort of thing already via PAM,
> > for example.
> 
> Ugh, hardly and it's hokey and a huge pain to do, and only works on
> platforms that have PAM.
> 
> Better is to use an external authentication system (Kerberos, for
> example) which can deal with this, but I do think this is also something
> we should be considering for core, especially now that we've got a
> reasonable password-based authentication method with SCRAM.

Does LDAP do this too?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


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

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

[Melvin Davidson]

On Tue, Sep 19, 2017 at 1:28 PM, Stephen Frost  wrote:

> Tom,
>
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > chiru r  writes:
> > > We are looking  for User profiles in ope source PostgreSQL.
> > > For example, If a  user password failed n+ times while login ,the user
> > > access has to be blocked few seconds.
> > > Please let us know, is there any plan to implement user profiles in
> feature
> > > releases?.
> >
> > Not particularly.  You can do that sort of thing already via PAM,
> > for example.
>
> Ugh, hardly and it's hokey and a huge pain to do, and only works on
> platforms that have PAM.
>
> Better is to use an external authentication system (Kerberos, for
> example) which can deal with this, but I do think this is also something
> we should be considering for core, especially now that we've got a
> reasonable password-based authentication method with SCRAM.
>
> Thanks!
>
> Stephen
>

Perhaps, as an alternative, although not currently supported, connection
attempts can be added in the future to "Event Triggers"?
Users could then create a trigger function to enable/disable logins.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL

[Stephen Frost]

Tom,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> chiru r  writes:
> > We are looking  for User profiles in ope source PostgreSQL.
> > For example, If a  user password failed n+ times while login ,the user
> > access has to be blocked few seconds.
> > Please let us know, is there any plan to implement user profiles in feature
> > releases?.
> 
> Not particularly.  You can do that sort of thing already via PAM,
> for example.

Ugh, hardly and it's hokey and a huge pain to do, and only works on
platforms that have PAM.

Better is to use an external authentication system (Kerberos, for
example) which can deal with this, but I do think this is also something
we should be considering for core, especially now that we've got a
reasonable password-based authentication method with SCRAM.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] [HACKERS] PLJava for Postgres 9.2.

[Paul Hammond]

Thx.

Yes, am aware PLJava is a 3rd party lib, just surprised the same party hasn't 
built them given they seem to be built all the way to 9.1.

My question was primarily about obtaining pgsx.mk file which is a part of the 
PostgreSQL project.

Paul



 From: Andrew Dunstan and...@dunslane.net
To: Paul Hammond hammpau...@yahoo.com 
Cc: pgsql-hackers@postgresql.org pgsql-hackers@postgresql.org; 
pgsql-gene...@postgresql.org pgsql-gene...@postgresql.org 
Sent: Friday, 17 May 2013, 0:03
Subject: Re: [GENERAL] [HACKERS] PLJava for Postgres 9.2.
 


On 05/16/2013 05:59 PM, Paul Hammond wrote:
 Hi all,

 I've downloaded PLJava, the latest version, which doesn't seem to have 
 a binary distribution at all for 9.2, so I'm trying to build it from 
 the source for Postgres 9.2. I have the DB itself installed on Windows 
 7 64 bit as a binary install. I've had to do a fair bit of hacking 
 with the makefiles on cygwin to get PLJava to build, but I have 
 succeeded in compiling the Java and JNI code, the pljava_all and 
 deploy_all targets effectively.


Cygwin is not a recommended build platform for native Windows builds. 
See the docs for the recommended ways to build Postgres.



 But I'm coming unstuck at the next target where it's doing the target 
 c_all. It's trying to find the following makefile in the Postgres dist:

 my postgres installation dir/lib/pgxs/src/makefiles/pgxs.mk: No such 
 file or directory

 What do I need to do to obtain the required files, and does anybody 
 know why, given Postgres 9.2 is out some time, and 9.3 is in beta, why 
 no prebuild binary PLJavas exist for 9.2?


Because nobody has built them?


FYI, PL/Java is not maintained by the PostgreSQL project.


cheers

andrew


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

Re: [GENERAL] [HACKERS] PLJava for Postgres 9.2.

[Cédric Villemain]

 Yes, am aware PLJava is a 3rd party lib, just surprised the same party
 hasn't built them given they seem to be built all the way to 9.1.
 
 My question was primarily about obtaining pgsx.mk file which is a part of
 the PostgreSQL project.

With linux you do something like that for pljava

$ make PG_CONFIG=/usr/pgsql-9.2/bin/pg_config \
JAVA_HOME=/usr/java/default

The pg_config is used to find the pgxs.mk (the real command is: «pg_config --
pgxs»).

 
 Paul
 
 
 
  From: Andrew Dunstan and...@dunslane.net
 To: Paul Hammond hammpau...@yahoo.com
 Cc: pgsql-hackers@postgresql.org pgsql-hackers@postgresql.org;
 pgsql-gene...@postgresql.org pgsql-gene...@postgresql.org Sent:
 Friday, 17 May 2013, 0:03
 Subject: Re: [GENERAL] [HACKERS] PLJava for Postgres 9.2.
 
 On 05/16/2013 05:59 PM, Paul Hammond wrote:
  Hi all,
  
  I've downloaded PLJava, the latest version, which doesn't seem to have
  a binary distribution at all for 9.2, so I'm trying to build it from
  the source for Postgres 9.2. I have the DB itself installed on Windows
  7 64 bit as a binary install. I've had to do a fair bit of hacking
  with the makefiles on cygwin to get PLJava to build, but I have
  succeeded in compiling the Java and JNI code, the pljava_all and
  deploy_all targets effectively.
 
 Cygwin is not a recommended build platform for native Windows builds.
 See the docs for the recommended ways to build Postgres.
 
  But I'm coming unstuck at the next target where it's doing the target
  c_all. It's trying to find the following makefile in the Postgres dist:
  
  my postgres installation dir/lib/pgxs/src/makefiles/pgxs.mk: No such
  file or directory
  
  What do I need to do to obtain the required files, and does anybody
  know why, given Postgres 9.2 is out some time, and 9.3 is in beta, why
  no prebuild binary PLJavas exist for 9.2?
 
 Because nobody has built them?
 
 
 FYI, PL/Java is not maintained by the PostgreSQL project.
 
 
 cheers
 
 andrew

-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation


signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Bruce Momjian]

On Tue, Mar 19, 2013 at 01:46:32AM -0400, Stephen Frost wrote:
  I guess that suggests we should be calling this something like
  'ssl_authorized_client_roots'.
 
 I'm no longer convinced that this really makes sense and I'm a bit
 worried about the simple authentication issue which I thought was at the
 heart of this concern.  Is there anything there that you see as being an
 issue with what we're doing currently..?

I too am worried that make SSL even more flexible will make simple setups
more complex to setup.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


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

Re: [GENERAL][HACKERS] register creation date of table

[Robert Haas]

On Fri, Oct 14, 2011 at 6:20 AM, Willy-Bas Loos willy...@gmail.com wrote:
 1. I think that there is no such information in the system tables. is
 that correct?

Yes.  It's been discussed before but some people (particularly, Tom,
IIRC) are not convinced that it's useful enough to justify its
existence.

 2. i would like to go back in time. I think that i will just look up
 the creation date for the files in the data directory and translate
 their oid's to the object names and then update their dates. This
 would of course only work from the last restore. Is that a good way to
 do it?

Well, that timestamp will get bumped on TRUNCATE, CLUSTER, VACUUM
FULL, and rewriting versions of ALTER TABLE.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme

[Magnus Hagander]

2010/4/1 Thom Brown thombr...@gmail.com:
 On 1 April 2010 09:13, Dave Page dp...@postgresql.org wrote:

 Following a great deal of discussion, I'm pleased to announce that the
 PostgreSQL Core team has decided that the major theme for the 9.1
 release, due in 2011, will be 'NoSQL'.

 There is a growing trend towards NoSQL databases, with major sites
 like Twitter and Facebook utilising them extensively. NoSQL databases
 often include multi-master replication, clustering and failover
 features that have long been requested in PostgresSQL, but have been
 extremely difficult to implement with SQL which has prevented us from
 advancing Postgree in the way that we'd like.

 To address this, the intention is to remove SQL support from
 Postgres, and replace it with a language called 'QUEL'. This will
 provide us with the flexibility we need to implement the features of
 modern NoSQL databases. With no SQL support there will obviously be
 some differences in the query syntax that must be used to access your
 data. For example, the query:

 select (e.salary/ (e.age - 18)) as comp from employee as e where
 e.name = Jones

 would be rewritten as:

 range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where
 e.name = Jones

 Aggregate syntax in QUEL is particularly powerful. For example, the query:

 select dept,
      avg(salary) as avg_salary,
      sum(salary) as tot_salary
 from
      employees
 group by
      dept

 may be written as:

 range of e is employee
 retrieve (e.dept,
         avg_salary = avg(e.salary by e.dept),
         tot_salary = sum(e.salary by e.dept)
 )

 Note that the grouped column can be specified for each individual
 aggregate.

 We will be producing a comprehensive guide to the QUEL syntax to aid
 with application migration. We appreciate the difficulty that this
 change may cause some users, but feel we must embrace the NoSQL
 philosophy in order to remain The world's most advanced Open Source
 database

 There's no question that, at 21 years old, the SQL standard is past its
 prime, said core developer and standards expert Peter Eisentraut. It's
 time for us to switch to something fresher.  I personally would have
 preferred XSLT, but QUEL is almost as good.

 Project committer Heikki Linnakangas added: By replacing SQL with
 QUEL not only will will be able to add new features to Postgres that
 were previously too difficult, but we'll also increase user loyalty as it'll
 be much harder for them to change to a different, SQL-based
 database. That'll be pretty cool.

 You may also notice that without SQL, the project name is somewhat
 misleading. To address that, the project name will be changed to
 'PostgreQUEL' with the 9.1 release. We expect this will also put an
 end to the periodic debates on changing the project name.

 Dave Page
 On behalf of the PostgreSQL Core Team


 I prefer to dump all my data in a big text file and grep it for the 
 information I need.

As long as you implement your own grep, that sounds about on par with
the current trends! Go for it!


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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

Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme

[David E. Wheeler]

On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote:

 I prefer to dump all my data in a big text file and grep it for the 
 information I need.
 
 As long as you implement your own grep, that sounds about on par with
 the current trends! Go for it!

Well, first you have to implement your own compiler. Also a lexer and a parser.

David


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

Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme

[Scott Marlowe]

On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler da...@kineticode.com wrote:
 On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote:

 I prefer to dump all my data in a big text file and grep it for the 
 information I need.

 As long as you implement your own grep, that sounds about on par with
 the current trends! Go for it!

 Well, first you have to implement your own compiler. Also a lexer and a 
 parser.

All that will be for naught unless you hand wire your own logic
boards.  I mean really, come on.

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

Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme

[Joshua D. Drake]

On Thu, 2010-04-01 at 10:54 -0600, Scott Marlowe wrote:
 On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler da...@kineticode.com 
 wrote:
  On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote:
 
  I prefer to dump all my data in a big text file and grep it for the 
  information I need.
 
  As long as you implement your own grep, that sounds about on par with
  the current trends! Go for it!
 
  Well, first you have to implement your own compiler. Also a lexer and a 
  parser.
 
 All that will be for naught unless you hand wire your own logic
 boards.  I mean really, come on.

I hate April 1st.

 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or 
Sir.


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

Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme

[Adrian Klaver]

On 04/01/2010 09:54 AM, Scott Marlowe wrote:

On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheelerda...@kineticode.com  wrote:

On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote:


I prefer to dump all my data in a big text file and grep it for the information 
I need.


As long as you implement your own grep, that sounds about on par with
the current trends! Go for it!


Well, first you have to implement your own compiler. Also a lexer and a parser.


All that will be for naught unless you hand wire your own logic
boards.  I mean really, come on.



Actually I think this calls for quantum computing 
(http://en.wikipedia.org/wiki/Quantum_computer). The intersection of 
NoSQL and Quantum decoherence is almost to good to be true.


--
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [GENERAL] [HACKERS] Bug on pg_lesslog

[Karl Denninger]

Joshua D. Drake wrote:
 On Thu, 2010-02-11 at 23:39 +0900, Koichi Suzuki wrote:
   
 Dear Folks;

 A very serious bug was reported on pg_lesslog.   So far, I found it's
 a bug in pg_compresslog.   Please do not use pg_compresslog and
 pg_decompresslog until improved version is uploaded.

 I strongly advise to take base backup of your database.

 I apologize for inconvenience.   I'll upload the new version ASAP.
 

 Should this go out on announce?
   
I certainly think so.  Anyone who gets caught by surprise on this
could quite possibly lose all their data!

I (fortunately) caught it during TESTING of my archives - before I
needed them.

-- Karl Denninger

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

Re: [GENERAL] [HACKERS] Bug on pg_lesslog

[Koichi Suzuki]

Thank you very much for the advice.   Yes I think it should go to
announce.   I will post a message.
--
Koichi Suzuki



2010/2/12 Karl Denninger k...@denninger.net:
 Joshua D. Drake wrote:

 On Thu, 2010-02-11 at 23:39 +0900, Koichi Suzuki wrote:


 Dear Folks;

 A very serious bug was reported on pg_lesslog.   So far, I found it's
 a bug in pg_compresslog.   Please do not use pg_compresslog and
 pg_decompresslog until improved version is uploaded.

 I strongly advise to take base backup of your database.

 I apologize for inconvenience.   I'll upload the new version ASAP.


 Should this go out on announce?


 I certainly think so.  Anyone who gets caught by surprise on this could
 quite possibly lose all their data!

 I (fortunately) caught it during TESTING of my archives - before I needed
 them.

 -- Karl Denninger



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

Re: [GENERAL] [HACKERS] Bug on pg_lesslog

[Koichi Suzuki]

In addition, in the fix, I'm thinking I should add at least the
following check mechanism;

1. Check XNOOP record size to match the original WAL record.
2. Restore WAL segment at the time of pg_compress, compare restored
WAL with the original and check it is safe to use in the restoration,
both each WAL record and whole WAL segment.

--
Koichi Suzuki



2010/2/12 Koichi Suzuki koichi@gmail.com:
 Thank you very much for the advice.   Yes I think it should go to
 announce.   I will post a message.
 --
 Koichi Suzuki



 2010/2/12 Karl Denninger k...@denninger.net:
 Joshua D. Drake wrote:

 On Thu, 2010-02-11 at 23:39 +0900, Koichi Suzuki wrote:


 Dear Folks;

 A very serious bug was reported on pg_lesslog.   So far, I found it's
 a bug in pg_compresslog.   Please do not use pg_compresslog and
 pg_decompresslog until improved version is uploaded.

 I strongly advise to take base backup of your database.

 I apologize for inconvenience.   I'll upload the new version ASAP.


 Should this go out on announce?


 I certainly think so.  Anyone who gets caught by surprise on this could
 quite possibly lose all their data!

 I (fortunately) caught it during TESTING of my archives - before I needed
 them.

 -- Karl Denninger




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

Re: [GENERAL] [HACKERS] Sugerencia de opcion

[Tom Lane]

Robert Haas robertmh...@gmail.com writes:
 2009/1/22 Informatica-Cooperativa Cnel. Oviedo informat...@coopovie.com.py:
     SELECT id, sum(salario) as SumaSalario
     FROM salarios
     GROUP BY id
     HAVING SumaSalario500;

 I've wished for that syntax once or twice myself, but I'm assuming
 there's a reason we haven't implemented it?

It's contrary to standard.  There are some other reasons you can find
in the archives, too.

regards, tom lane

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

Re: [GENERAL] [HACKERS] Fwd: psql+krb5

[Scott Marlowe]

Except that he posted a month ago and got no answers...

On Tue, Dec 1, 2009 at 8:22 AM, Robert Haas robertmh...@gmail.com wrote:
 2009/11/30 rahimeh khodadadi rahimeh.khodad...@gmail.com:


 -- Forwarded message --
 From: rahimeh khodadadi rahimeh.khodad...@gmail.com
 Date: 2009/11/29
 Subject: Re: psql+krb5
 To: Denis Feklushkin denis.feklush...@gmail.com

 Please review the guidelines for reporting a problem, which you can find here:

 http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

 It seems to me that you've done the exact opposite of nearly
 everything suggested there, starting with cross-posting your email to
 four mailing lists at least three of which are irrelevant to the
 problem that you're attempting to solve.

 ...Robert

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




-- 
When fascism comes to America, it will be intolerance sold as diversity.

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

Re: [GENERAL] [HACKERS] Fwd: psql+krb5

[Robert Haas]

On Tue, Dec 1, 2009 at 11:26 AM, Scott Marlowe scott.marl...@gmail.com wrote:
 Except that he posted a month ago and got no answers...

Gee, I wonder why.

...Robert

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

Re: [GENERAL] [HACKERS] libpq port number handling

[Magnus Hagander]

On 25 sep 2009, at 02.59, Tom Lane t...@sss.pgh.pa.us wrote:


Sam Mason s...@samason.me.uk writes:

+if (portnum  1 || portnum  65535)


BTW, it strikes me that we could tighten this even more by rejecting
target ports below 1024.  This is guaranteed safe on all Unix systems
I know of, because privileged ports can only be listened to by root- 
owned

processes and we know the postmaster won't be one.  I am not sure
whether it would be possible to start the postmaster on a low-numbered
port on Windows though.  Anyone know?  Even if it's possible, do we
want to allow it?


Windows doesn't care. A non privileged process can open any port, both  
above and below 1024.


Other than that, I agree with previous comments - restricting this in  
libpq won't actually help anything, but in a few limited cases it will  
be very annoying.


/Magnus





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

Re: [GENERAL] [HACKERS] libpq port number handling

[Peter Eisentraut]

On Thu, 2009-09-24 at 20:36 -0400, Tom Lane wrote:
 BTW, are port numbers still limited to 16 bits in IPv6?

Port numbers are in TCP, not in IP.


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

Re: [GENERAL] [HACKERS] libpq port number handling

[Sam Mason]

On Fri, Sep 25, 2009 at 09:29:24AM +0300, Peter Eisentraut wrote:
 On Thu, 2009-09-24 at 20:36 -0400, Tom Lane wrote:
  BTW, are port numbers still limited to 16 bits in IPv6?
 
 Port numbers are in TCP, not in IP.

I'd checked that it should work with IPv6, but I hadn't realized that
it was because ports were at a different level of abstraction.  This
mailing list is good for otherwise obscure details like that!

-- 
  Sam  http://samason.me.uk/

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

Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from unknown to text

[Scott Marlowe]

On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh singh.gurj...@gmail.com wrote:
 I took your cue, and have formulated this solution for 8.3.1 :

Is there a good reason you're running against a db version with known
bugs instead of 8.3.5?  Seriously, it's an easy upgrade and running a
version missing over a year of updates is not a best practice.

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

Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from unknown to text

[Gurjeet Singh]

On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh singh.gurj...@gmail.com
 wrote:
  I took your cue, and have formulated this solution for 8.3.1 :

 Is there a good reason you're running against a db version with known
 bugs instead of 8.3.5?  Seriously, it's an easy upgrade and running a
 version missing over a year of updates is not a best practice.


That's just a development instance that I have kept for long; actual issue
was on EDB 8.3.0.12, which the customer is using. As noted in the PS of
previous mail, the solution that worked for PG 8.3.1 didn't work on EDB
8.3.0.12, so had to come up with a different code for that!

Best regards,
-- 
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from unknown to text

[Scott Marlowe]

On Tue, Jan 6, 2009 at 2:24 AM, Gurjeet Singh singh.gurj...@gmail.com wrote:
 On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe scott.marl...@gmail.com
 wrote:

 On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh singh.gurj...@gmail.com
 wrote:
  I took your cue, and have formulated this solution for 8.3.1 :

 Is there a good reason you're running against a db version with known
 bugs instead of 8.3.5?  Seriously, it's an easy upgrade and running a
 version missing over a year of updates is not a best practice.

 That's just a development instance that I have kept for long; actual issue
 was on EDB 8.3.0.12, which the customer is using. As noted in the PS of
 previous mail, the solution that worked for PG 8.3.1 didn't work on EDB
 8.3.0.12, so had to come up with a different code for that!

Ahh, ok.  I was just worried you were ignoring updates.  I don't know
anything about the numbering scheme for EDB.  What does 8.3.0.12
translate to in regular pgsql versions?

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

Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from unknown to text

[Tom Lane]

Gurjeet Singh singh.gurj...@gmail.com writes:
 create cast (unknown as text) with function unknown2text( unknown ) as
 implicit;

This is a horrendously bad idea; it will bite your *ss sooner or later,
probably sooner.

regards, tom lane

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

Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from unknown to text

[Gurjeet Singh]

On Tue, Jan 6, 2009 at 6:31 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Gurjeet Singh singh.gurj...@gmail.com writes:
  create cast (unknown as text) with function unknown2text( unknown ) as
  implicit;

 This is a horrendously bad idea; it will bite your *ss sooner or later,
 probably sooner.

regards, tom lane


I guessed so, but couldn't figure out exactly how! That's why I have
suggested this as a temp solution until we confirmed this with someone more
knowledgeable.

Can you please let us know how this would be problematic? And can you
suggest a better solution?

Thanks and best regards,
-- 
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from unknown to text

[Tom Lane]

Gurjeet Singh singh.gurj...@gmail.com writes:
 This is a horrendously bad idea; it will bite your *ss sooner or later,
 probably sooner.

 Can you please let us know how this would be problematic?

The point is that it's going to have unknown, untested effects on the
default coercion rules, possibly leading to silent changes in the
behavior of queries that used to work.  If you'd rather retest every one
of your other queries than fix this one, then go ahead.

regards, tom lane

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

Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from unknown to text

[Gurjeet Singh]

On Tue, Jan 6, 2009 at 7:18 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Gurjeet Singh singh.gurj...@gmail.com writes:
  This is a horrendously bad idea; it will bite your *ss sooner or later,
  probably sooner.

  Can you please let us know how this would be problematic?

 The point is that it's going to have unknown, untested effects on the
 default coercion rules, possibly leading to silent changes in the
 behavior of queries that used to work.  If you'd rather retest every one
 of your other queries than fix this one, then go ahead.


Changing the query is an option not given to us. It is being migrated from a
BigDB.

I was working on these solutions assuming that these are workarounds to a
bug. But from your mails, it seems that it is an expected behaviour; is it?

If we consider the second branch of UNION ALL of both the queries above, if
select ''  yields a text column, then so should a select * from (select
'').

Its not exactly a bug, but sure is a problem that we should try to resolve.

Thanks and best regards,
-- 
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from unknown to text

[Martijn van Oosterhout]

On Tue, Jan 06, 2009 at 11:13:59PM +0530, Gurjeet Singh wrote:
 If we consider the second branch of UNION ALL of both the queries above, if
 select ''  yields a text column, then so should a select * from (select
 '').

The problem is ofcourse that select '' doesn't produce a text column
in postgres. This generally works fine, except in the case of UNION
where none of the branches provide the necessary type info.

Have a nice day,
-- 
Martijn van Oosterhout   klep...@svana.org   http://svana.org/kleptog/
 Please line up in a tree and maintain the heap invariant while 
 boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Simon Riggs]

On Mon, 2008-10-20 at 18:45 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  On Mon, 2008-10-20 at 17:44 -0300, Alvaro Herrera wrote:
  That's been extended with an epoch counter per the docs; I don't think
  that's appropriate for the new functions, is it?
 
  I assumed it was, so you can subtract them easily. 
 
  It can be done either way, I guess. Happy to provide what people need. I
  just dreamed up a few that sounded useful.
 
 I don't think you should be inventing new functions without clear
 use-cases in mind.  Depending on what the use is, I could see either the
 xid or the txid definition as being *required*.

The use case for the two functions was clearly stated as together
allows easy arithmetic on xid difference between master and
slave. In that context, xid plus epoch is appropriate.

There are other use cases. We can have both, neither or just one,
depending upon what people think. What would you want xid only for? Do
you think that should replace the txid one?

This is everybody's opportunity to say what we need.

 In any case, do not use the wrong return type for the definition you're
 implementing.

err...Why would anyone do that?

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 On Mon, 2008-10-20 at 18:45 -0400, Tom Lane wrote:
 In any case, do not use the wrong return type for the definition you're
 implementing.

 err...Why would anyone do that?

That's what I wanted to know ;-).  If these functions are really going
to return txid, then they should be named to reflect that.

regards, tom lane

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

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Alvaro Herrera]

Simon Riggs escribió:
 
 On Mon, 2008-10-20 at 16:22 -0400, Robert Haas wrote:
   * pg_last_recovered_xact_xid()
   Will throw an ERROR if *not* executed in recovery mode.
   returns bigint
  
   * pg_last_completed_xact_xid()
   Will throw an ERROR *if* executed in recovery mode.
   returns bigint
  
  Should these return xid?
 
 Perhaps, but they match txid_current() which returns bigint.
 http://developer.postgresql.org/pgdocs/postgres/functions-info.html

That's been extended with an epoch counter per the docs; I don't think
that's appropriate for the new functions, is it?

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

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

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Simon Riggs]

On Mon, 2008-10-20 at 17:44 -0300, Alvaro Herrera wrote:
 Simon Riggs escribió:
  
  On Mon, 2008-10-20 at 16:22 -0400, Robert Haas wrote:
* pg_last_recovered_xact_xid()
Will throw an ERROR if *not* executed in recovery mode.
returns bigint
   
* pg_last_completed_xact_xid()
Will throw an ERROR *if* executed in recovery mode.
returns bigint
   
   Should these return xid?
  
  Perhaps, but they match txid_current() which returns bigint.
  http://developer.postgresql.org/pgdocs/postgres/functions-info.html
 
 That's been extended with an epoch counter per the docs; I don't think
 that's appropriate for the new functions, is it?

I assumed it was, so you can subtract them easily. 

It can be done either way, I guess. Happy to provide what people need. I
just dreamed up a few that sounded useful.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 On Mon, 2008-10-20 at 17:44 -0300, Alvaro Herrera wrote:
 That's been extended with an epoch counter per the docs; I don't think
 that's appropriate for the new functions, is it?

 I assumed it was, so you can subtract them easily. 

 It can be done either way, I guess. Happy to provide what people need. I
 just dreamed up a few that sounded useful.

I don't think you should be inventing new functions without clear
use-cases in mind.  Depending on what the use is, I could see either the
xid or the txid definition as being *required*.

In any case, do not use the wrong return type for the definition you're
implementing.

regards, tom lane

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

Re: [GENERAL] [HACKERS] New shapshot RPMs (Sep 7 2008) are ready for testing

[Devrim GÜNDÜZ]

Hi,

On Sun, 2008-09-07 at 13:39 -0400, Andrew Dunstan wrote:

 The point I was making is that for 8.4, unless you specifically 
 configure with --disable-integer-datetimes, it is enabled by default on 
 any platform that can support it. We committed that change on 30 March 
 here: http://archives.postgresql.org/pgsql-committers/2008-03/msg00550.php

You are right, and I overlooked the actual macro.

I now fixed the macro by changing its behavior to use the
--disable-integer-datetimes mode if not defined.

Cheers, 
-- 
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
   http://www.gunduz.org


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] [HACKERS] New shapshot RPMs (Sep 7 2008) are ready for testing

[Devrim GÜNDÜZ]

Hello,

On Sun, 2008-09-07 at 11:01 -0400, Andrew Dunstan wrote:

 Hasn't integer-datetimes been the default for a while? 

No. I added it as a macro to 8.3, but did not enable it by default,
because I am trying to be binary compatible with Red Hat / Fedora RPMs.

I believe Tom will also add it to 8.4 packages for Fedora 11.

Regards,
-- 
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
   http://www.gunduz.org


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] [HACKERS] New shapshot RPMs (Sep 7 2008) are ready for testing

[Joshua D. Drake]

Andrew Dunstan wrote:




Hasn't integer-datetimes been the default for a while? Of course, a 
catversion bump will force a dump/reload regardless of that.


Unfortunately not. It is the default on some versions of linux such as 
Debian/Ubuntu.


Sincerely,

Joshua D. Drake


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

Re: [GENERAL] [HACKERS] New shapshot RPMs (Sep 7 2008) are ready for testing

[Tom Lane]

Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= [EMAIL PROTECTED] writes:
 On Sun, 2008-09-07 at 11:01 -0400, Andrew Dunstan wrote:
 Hasn't integer-datetimes been the default for a while? 

 No. I added it as a macro to 8.3, but did not enable it by default,
 because I am trying to be binary compatible with Red Hat / Fedora RPMs.
 I believe Tom will also add it to 8.4 packages for Fedora 11.

The RHEL/Fedora RPMs have never specified this one way or the other,
so they'd just get the default.  I haven't really thought about whether
to change that when 8.4 comes out.  I'd prefer to follow upstream's
lead on the point; but if for instance we could do update-in-place
so long as you hadn't switched datetime representation, that would be a
pretty powerful incentive to stick with the float format.

regards, tom lane

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

Re: [GENERAL] [HACKERS] New shapshot RPMs (Sep 7 2008) are ready for testing

[Andrew Dunstan]

Joshua D. Drake wrote:

Andrew Dunstan wrote:




Hasn't integer-datetimes been the default for a while? Of course, a 
catversion bump will force a dump/reload regardless of that.


Unfortunately not. It is the default on some versions of linux such as 
Debian/Ubuntu.





The point I was making is that for 8.4, unless you specifically 
configure with --disable-integer-datetimes, it is enabled by default on 
any platform that can support it. We committed that change on 30 March 
here: http://archives.postgresql.org/pgsql-committers/2008-03/msg00550.php


cheers

andrew

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

Re: [GENERAL] [HACKERS] Stored procedure issue

[Pavel Stehule]

Hello

On 03/12/2007, Dragan Zubac [EMAIL PROTECTED] wrote:
 Hello

 What I have noticed is that when I don't use procedure
 at all,there's only 2-5 locks in pg_locks,after I
 start application which uses stored procedure the
 number in pg_locks increase rapidly to steady 75 even
 to 130 at certain moments.

 Any clue why procedure usage might increase locks so
 heavily ?

 Sincerely


upgrade to 8.2? There is shared lock and there are less problems with
locks. But I am not sure if this solves your problem. General
protection before locks is all things with maximal speed. Are your
queris well optimazed?

Regards
Pavel Stehule

 --- Dragan Zubac [EMAIL PROTECTED] wrote:

  Hello
 
  I have a stored procedure which does the billing
  stuff
  in our system,it works ok,but if I put in
  production,where there is some 5-10 billing events
  per
  second,the whole database slows down. It won't even
  drop some test table,reindex,vacuum,things which
  were
  done before in the blink of an eye. If I stop the
  application which calls the procedure,all is back to
  normal.
 
  We didn't implement any special locking mechanism in
  the procedure,all is default. The procedure is
  updating user's balance in table 'users'. On the
  other
  hand a couple of 'heavy load' table has foreign keys
  pointing to table 'users'.
 
  Is it the matter of concurency and some locking
  issue
  or maybe the existing of all those foreign keys
  pointing to table 'users',or maybe something else
  which we're not aware at the moment ?
 
  Sincerely
 
  Pera
 
 
 
 
 
  Be a better sports nut!  Let your teams follow you
  with Yahoo Mobile. Try it now.
 
 http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
 
  ---(end of
  broadcast)---
  TIP 7: You can help support the PostgreSQL project
  by donating at
 
 
  http://www.postgresql.org/about/donate
 



   
 
 Be a better friend, newshound, and
 know-it-all with Yahoo! Mobile.  Try it now.  
 http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ


 ---(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 3: Have you checked our extensive FAQ?

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

Re: [GENERAL] [HACKERS] Stored procedure issue

[Dragan Zubac]

Hello

Here's the stored procedure itself,as well as the
related tables involved in it's calculations.
The idea for procedure is to find longest prefix match
for destination number,try to find it in table
'billing' for particular users,find the price,and
insert message into history and inqueue table,as well
as to decreace the user's balance in table 'users'.
Would it help to put all prefices,prices data in some
sort of cache and let procedure first try to match
with data from cache and if it can't find to try to
get data from table itself from hard disk ?

I'm looking for some solution where this procedure can
operate at higher loads and to leave other parts of
database operational as much as it could.

--Procedure---

create type dajbre as (status int,id bigint);

CREATE OR REPLACE FUNCTION
proc_uni(integer,integer,inet,text,integer,integer,text,integer,integer,
text,int, int,boolean,text) RETURNS setof dajbre AS '

DECLARE

uid alias for $1;
pid alias for $2;
ip_i alias for $3;
s_number alias for $4;
s_ton_i alias for $5;
s_npi_i alias for $6;
d_number alias for $7;
d_ton_i alias for $8;
d_npi_i alias for $9;
mess alias for $10;
dcs_i alias for $11;
esm_i alias for $12;
delivery_i alias for $13;
u_mess_id_i alias for $14;

r dajbre%rowtype;

prefixfound boolean;
prefixprice billing.price%TYPE;
dest_num_len int;
tmp_dest_number text;
tmp_user_bal numeric;
tmp_returnval int;
novi_status int;
tmp_his_id bigint;
tmp_u_mess_id_i text;


begin


dest_num_len := char_length(d_number);
tmp_dest_number := d_number;
prefixfound := false;


while dest_num_len  0 loop

select into prefixprice price from billing
where u_id=uid and prefix=tmp_dest_number;

if not found then
tmp_dest_number := substring
(tmp_dest_number from 1 for dest_num_len-1);
dest_num_len :=
char_length(tmp_dest_number);
else 
prefixfound := true;
exit;
end if;
end loop;


if prefixfound=false then
tmp_returnval :=11;
novi_status :=11;  
else if prefixprice = 0 then
tmp_returnval :=11; 
novi_status :=50;   
 else select into tmp_user_bal maxsms-cursms from
users where id=uid;
if tmp_user_bal  prefixprice then 
tmp_returnval :=11;
novi_status :=51;  
else
tmp_returnval :=0;
end if;
 end if;   
end if;


if tmp_returnval = 0 then


insert into history
(ip,source,dest,message,dcs,esm,s_ton,s_npi,d_ton,d_npi,u_id,delivery,price,p_id,u_mess_id)
values
(ip_i,s_number,d_number,decode(mess,''base64''),dcs_i,esm_i,s_ton_i,s_npi_i,d_ton_i,d_npi_i,uid,delivery_i,prefixprice,pid,u_mess_id_i);

 tmp_his_id := currval(''history_id_seq'');



if pid = 2 then
if u_mess_id_i = 0 then
tmp_u_mess_id_i := ;
else
tmp_u_mess_id_i := u_mess_id_i;
end if;
else if pid = 3 then
  tmp_u_mess_id_i := tmp_his_id ;
 end if;
end if;

update history set u_mess_id = tmp_u_mess_id_i where
id = tmp_his_id;
update users set cursms=cursms+ prefixprice where
id=uid;


insert into inqueue(id, u_id) values (tmp_his_id,
uid);

r.status := 0;
r.id := tmp_his_id;
return next r;


else


insert into rejected
(ip,source,dest,message,dcs,esm,s_ton,s_npi,d_ton,d_npi,status,u_id,delivery,u_mess_id)
values
(ip_i,s_number,d_number,decode(mess,''base64''),dcs_i,esm_i,s_ton_i,s_npi_i,d_ton_i,d_npi_i,novi_status,uid,delivery_i,u_mess_id_i);

r.status := 11;
r.id := 0;
return next r;

end if;

return;

end;

' language 'plpgsql';
-

---Billing table-
   Table public.billing 
  

   Column   |  Type  | 
Modifiers
++--
 id | integer| not null default
nextval('billing_id_seq'::regclass)
 u_id   | integer| not null
 prefix | text   |
 operator   | integer|
 price  | numeric(20,10) |
 comment| text   |
 new_prefix | boolean| default false
Indexes:
billing_pkey PRIMARY KEY, btree (id)
bil_uid btree (u_id)
Foreign-key constraints:
$1 FOREIGN KEY (u_id) REFERENCES users(id)
$2 FOREIGN KEY (operator) REFERENCES
operators(id)
-
Users table--
   Column   |  Type  |
Modifiers
++
 id | integer| not null
default nextval('users_id_seq'::regclass)
 username   | text   | not null
 password   | text   | not null
 name   | text   |
 email  | text   |
 mobile | text   |
 phone  | text   |
 company 

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

[Bruno Wolff III]

On Fri, Feb 23, 2007 at 18:14:25 -0500,
  Tom Lane [EMAIL PROTECTED] wrote:
 Merlin Moncure [EMAIL PROTECTED] writes:
  On friday we upgraded a critical backend server to postgresql 8.2
  running on fedora core 4.
 
 Umm ... why that particular choice of OS?  Red Hat dropped update
 support for FC4 some time ago, and AFAIK the Fedora Legacy project
 is not getting things done.  How old is the kernel you're using?

The Fedora Legacy project is officially gone now.

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

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

[Bruno Wolff III]

On Mon, Feb 26, 2007 at 15:57:02 +0200,
  Devrim GUNDUZ [EMAIL PROTECTED] wrote:
 
 Upgrading OS will probably solve your problem; since there is no way to
 upgrade FC4 kernel unless you want to compile kernel source on your
 system.

And good luck with that. Fedora still back patches stuff from later kernels
than the one you think you have based on the name. Building a Linus kernel
and getting the right mix of versions to work on a particular version of
Fedora might be hard to do. If you can find the patch that fixes the
problem, your best bet (assuming you have to use FC4) would be to try to apply
that fix to the latest Fedora kernel for FC4.

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

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

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

[Devrim GUNDUZ]

Hi,

On Mon, 2007-02-26 at 08:24 -0500, Merlin Moncure wrote:
 we tried update to the latest via yum update with no help.

As Tom stated, FC4 is no more supported; therefore you won't be able to
get newer kernel via yum.

 as promised, here is the  best photo of the panic we could get:
 http://img144.imageshack.us/my.php?image=dumpic6.jpg

...bad locking...

The picture reminded me a SCSI driver bug in older kernels -- I google'd
again now and I saw a post that says native drivers are being used in
FC5+ kernels. If this is the real case, you may hit the problem
sometime later.

Upgrading OS will probably solve your problem; since there is no way to
upgrade FC4 kernel unless you want to compile kernel source on your
system.

Regards,

-- 
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/




signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

[CAJ CAJ]

On 2/23/07, Tom Lane [EMAIL PROTECTED] wrote:


Merlin Moncure [EMAIL PROTECTED] writes:
 On friday we upgraded a critical backend server to postgresql 8.2
 running on fedora core 4.

Umm ... why that particular choice of OS?  Red Hat dropped update
support for FC4 some time ago, and AFAIK the Fedora Legacy project
is not getting things done.  How old is the kernel you're using?

 At this juncture we are going to downgrade the postmaster back to 8.1
 and see if that fixes the panics.

Even assuming that Postgres is related to the panics, I don't think you
will find anyone maintaining that a kernel panic is not the kernel's
problem.  If an application *is* able to provoke a kernel panic, the
standard description of the problem would be critical kernel security
flaw.



I vaguely remember running into spinlock problems with FC4 and it wasn't due
to PostgreSQL. We didn't have database running on FC4.

If you are running a critical server you should switch to atleast CentOS.

Re: [GENERAL] [HACKERS] getting status transaction error

[Alvaro Herrera]

Merlin Moncure wrote:

 ya, it doesn't seem to match, as this seems to be repeating quite
 regularly.  interesting that my 'clog' files start at 06B6 and count
 up. 0207 is way off the charts.
 
 a lot of applications are hitting this database, and so far everything
 seems to be running ok (i found this log msg by accident), but I am
 now officially very nervous.

I don't think there's much cause for concern here.  If my theory is
correct, this is an autovacuum bug which was fixed in 8.1.7.

What I'd do is create a 0207 clog file, fill it with 0x55 (which is
transactions committed for all transactions in that interval), and do
a VACUUM FREEZE on that database.  You'll need to set
pg_database.datallowconn=true beforehand.

Of course, I'd copy the files somewhere else and experiment on a scratch
postmaster, running on a different port, just to be sure ...

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

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

Re: [GENERAL] [HACKERS] getting status transaction error

[Merlin Moncure]

n 2/6/07, Alvaro Herrera [EMAIL PROTECTED] wrote:

Merlin Moncure wrote:

 ya, it doesn't seem to match, as this seems to be repeating quite
 regularly.  interesting that my 'clog' files start at 06B6 and count
 up. 0207 is way off the charts.

 a lot of applications are hitting this database, and so far everything
 seems to be running ok (i found this log msg by accident), but I am
 now officially very nervous.

I don't think there's much cause for concern here.  If my theory is
correct, this is an autovacuum bug which was fixed in 8.1.7.

What I'd do is create a 0207 clog file, fill it with 0x55 (which is
transactions committed for all transactions in that interval), and do
a VACUUM FREEZE on that database.  You'll need to set
pg_database.datallowconn=true beforehand.

Of course, I'd copy the files somewhere else and experiment on a scratch
postmaster, running on a different port, just to be sure ...


thats a big help, database is actually fairly huge, so I may have to
just go ahead and do it.   I'm off to a meeting, but I'll check back
when I'm done and assuming nobody else says 'don't do that', I'll try
the fix and post back with the result.

thanks all,
merlin

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

Re: [GENERAL] [HACKERS] getting status transaction error

[Alvaro Herrera]

Merlin Moncure wrote:
 n 2/6/07, Alvaro Herrera [EMAIL PROTECTED] wrote:
 Merlin Moncure wrote:
 
  ya, it doesn't seem to match, as this seems to be repeating quite
  regularly.  interesting that my 'clog' files start at 06B6 and count
  up. 0207 is way off the charts.
 
  a lot of applications are hitting this database, and so far everything
  seems to be running ok (i found this log msg by accident), but I am
  now officially very nervous.
 
 I don't think there's much cause for concern here.  If my theory is
 correct, this is an autovacuum bug which was fixed in 8.1.7.
 
 What I'd do is create a 0207 clog file, fill it with 0x55 (which is
 transactions committed for all transactions in that interval), and do
 a VACUUM FREEZE on that database.  You'll need to set
 pg_database.datallowconn=true beforehand.
 
 Of course, I'd copy the files somewhere else and experiment on a scratch
 postmaster, running on a different port, just to be sure ...
 
 thats a big help, database is actually fairly huge, so I may have to
 just go ahead and do it.   I'm off to a meeting, but I'll check back
 when I'm done and assuming nobody else says 'don't do that', I'll try
 the fix and post back with the result.

Well, you don't need to copy all databases for the test area, just the
base/oid dir for template0 (along with all pg_xlog and pg_clog files,
etc, but these shouldn't be as big as all the other stuff in base/).

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

---(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] [HACKERS] New project launched : PostgreSQL GUI Installer for

[Steve Atkins]

On Jan 30, 2006, at 8:48 PM, Tony Caduto wrote:


Devrim GUNDUZ wrote:

Have you looked at AutoPackage?

http://autopackage.org

screen shots.

http://autopackage.org/gallery.html

Has a GUI wizard if X windows is available and a command line  
wizard if no X is available.



Using autopackage is similar to using MSI,Wise,Inno etc on Windows.


If that's the one that uses aptools it looks _excellent_. Until you try
and use it. It looked as though it would solve many of my packaging
problems, not least deploying on older platforms than the build box,
but simply didn't work on anything more complex than toy code.

I suspect that if you were just using it as a general installer, rather
than any of the portability magic, it might be worth a look.

Cheers,
  Steve

---(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] [HACKERS] Troubles with array_ref

[Cristian Prieto]

Thanks a lot man!!! You saved my life :P

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Martes, 08 de Noviembre de 2005 04:13 p.m.
To: Cristian Prieto
Cc: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [GENERAL] [HACKERS] Troubles with array_ref 

Cristian Prieto [EMAIL PROTECTED] writes:
 Well, anyway, this is the Stored Function I've been workin on; it simply
 take an array and an integer just to return this item from the array; The
 array could have any kind of elements so I declare it as anyarray (the
 parameter) and anyelement (the return value), please help me, I don't know
 where to get info about it.

You could save yourself a lot of time if you enabled warnings from your
C compiler (eg, -Wall for gcc) and then paid some attention to them.
The last parameter to array_ref is a bool *, not a bool, and I have no
doubt that the backend is crashing while trying to dereference false.

(Another problem is that the fourth parameter should be -1 not VARSIZE.)

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


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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Bruce Momjian]

Is there any TODO here?

---

Tom Lane wrote:
 Dann Corbit [EMAIL PROTECTED] writes:
  I guess that additional ambiguity arises if you add additional spaces to
  the end.  Many database systems solve this by trimming the characters
  from the end of the string upon storage and the returned string will not
  have any trailing blanks.
 
 Can you document that?  ISTM that that would effectively make char(n)
 and varchar(n) exactly equivalent, which is ... um ... a bit stupid.
 
   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
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

Document the collating sequences used for the character types.

 -Original Message-
 From: Bruce Momjian [mailto:[EMAIL PROTECTED]
 Sent: Monday, October 24, 2005 11:01 AM
 To: Tom Lane
 Cc: Dann Corbit; [EMAIL PROTECTED];
[EMAIL PROTECTED];
 pgsql-hackers@postgresql.org
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 
 Is there any TODO here?
 


--
 -
 
 Tom Lane wrote:
  Dann Corbit [EMAIL PROTECTED] writes:
   I guess that additional ambiguity arises if you add additional
spaces
 to
   the end.  Many database systems solve this by trimming the
characters
   from the end of the string upon storage and the returned string
will
 not
   have any trailing blanks.
 
  Can you document that?  ISTM that that would effectively make
char(n)
  and varchar(n) exactly equivalent, which is ... um ... a bit stupid.
 
  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
 
 
 --
   Bruce Momjian|  http://candle.pha.pa.us
   pgman@candle.pha.pa.us   |  (610) 359-1001
   +  If your life is a hard drive, |  13 Roberts Road
   +  Christ can be your backup.|  Newtown Square, Pennsylvania
 19073

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Bruce Momjian]

Dann Corbit wrote:
 Document the collating sequences used for the character types.

Sorry, I don't understand that.  What does it mean?

---


 
  -Original Message-
  From: Bruce Momjian [mailto:[EMAIL PROTECTED]
  Sent: Monday, October 24, 2005 11:01 AM
  To: Tom Lane
  Cc: Dann Corbit; [EMAIL PROTECTED];
 [EMAIL PROTECTED];
  pgsql-hackers@postgresql.org
  Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
  
  
  Is there any TODO here?
  
 
 
 --
  -
  
  Tom Lane wrote:
   Dann Corbit [EMAIL PROTECTED] writes:
I guess that additional ambiguity arises if you add additional
 spaces
  to
the end.  Many database systems solve this by trimming the
 characters
from the end of the string upon storage and the returned string
 will
  not
have any trailing blanks.
  
   Can you document that?  ISTM that that would effectively make
 char(n)
   and varchar(n) exactly equivalent, which is ... um ... a bit stupid.
  
 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
  
  
  --
Bruce Momjian|  http://candle.pha.pa.us
pgman@candle.pha.pa.us   |  (610) 359-1001
+  If your life is a hard drive, |  13 Roberts Road
+  Christ can be your backup.|  Newtown Square, Pennsylvania
  19073
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

 -Original Message-
 From: Bruce Momjian [mailto:[EMAIL PROTECTED]
 Sent: Monday, October 24, 2005 5:57 PM
 To: Dann Corbit
 Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED];
 pgsql-hackers@postgresql.org
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 Dann Corbit wrote:
  Document the collating sequences used for the character types.
 
 Sorry, I don't understand that.  What does it mean?

According to the standard, comparisons against character types are
supposed to PAD with either space or PAD CHAR according to the
collating sequence and not according to the data type.

Since the padding behavior of PosgreSQL comparisons for character types
is different according to the data type and not the collating sequence,
what is needed is simply to define the collating sequence used for
different character types.


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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Bruce Momjian]

Dann Corbit wrote:
  -Original Message-
  From: Bruce Momjian [mailto:[EMAIL PROTECTED]
  Sent: Monday, October 24, 2005 5:57 PM
  To: Dann Corbit
  Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED];
  pgsql-hackers@postgresql.org
  Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
  
  Dann Corbit wrote:
   Document the collating sequences used for the character types.
  
  Sorry, I don't understand that.  What does it mean?
 
 According to the standard, comparisons against character types are
 supposed to PAD with either space or PAD CHAR according to the
 collating sequence and not according to the data type.
 
 Since the padding behavior of PosgreSQL comparisons for character types
 is different according to the data type and not the collating sequence,
 what is needed is simply to define the collating sequence used for
 different character types.

But isn't collating sequence related to ordering?  How does this relate
to padding?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Bruce Momjian]

Dann Corbit wrote:
  But isn't collating sequence related to ordering?  How does this
 relate
  to padding?
 
 Right.  Collating sequence is how ordering is defined.  But when you
 compare two character types, they are supposed to pad according to the
 collating sequence.  So whether you blank fill or pad with some special
 character when performing a comparison is defined by the collating
 sequence and not by the character type.  Since we see (for instance)
 that bpchar(n) and varchar(n) pad differently when performing a
 comparison, we must assume that they have a different collating
 sequence.  So the question is what is it?
 
 It is always possible that I have misread the standard.

OK, I understand now.  It is tempting to think that the difference
between char() and varchar() is that internally they use a different
collating sequences, but that isn't the case.  If it were, space would
be ignored during comparisons any place in the string, when in fact, is
it is only trailing space that char() ignores, e.g.:

test= SELECT 'a '::CHAR(10) = 'a'::CHAR(10);
 ?column?
--
 t
(1 row)

test= SELECT 'a '::VARCHAR(10) = 'a'::VARCHAR(10);
 ?column?
--
 f
(1 row)

test= SELECT 'a'::CHAR(10) = ' a'::CHAR(10);
 ?column?
--
 f
(1 row)

test= SELECT 'a'::VARCHAR(10) = ' a'::VARCHAR(10);
 ?column?
--
 f
(1 row)

Our docs already have:

http://candle.pha.pa.us/main/writings/pgsql/sgml/datatype-character.html

Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
padding spaces are treated as semantically insignificant. Trailing
spaces are disregarded when comparing two values of type character, and
they will be removed when converting a character value to one of the
other string types. Note that trailing spaces are semantically
significant in character varying and text values.

What additional documentation is needed?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Tom Lane]

Bruce Momjian pgman@candle.pha.pa.us writes:
 OK, I understand now.  It is tempting to think that the difference
 between char() and varchar() is that internally they use a different
 collating sequences, but that isn't the case.  If it were, space would
 be ignored during comparisons any place in the string, when in fact, is
 it is only trailing space that char() ignores, e.g.:

No, you don't understand.  The standard defines PAD SPACE as making
*trailing* spaces irrelevant to comparisons, not embedded or leading
spaces.  And they regard PAD SPACE as an attribute of a collation rather
than of the string datatype.  This seems pretty wacko to me (in
particular it's hard to see how NO PAD is useful with char(N) storage)
but that's what they did.

 What additional documentation is needed?

Some specific discussion of the relationship to the standard would be
helpful, perhaps.

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] [HACKERS] 'a' == 'a '

[Bruce Momjian]

Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  OK, I understand now.  It is tempting to think that the difference
  between char() and varchar() is that internally they use a different
  collating sequences, but that isn't the case.  If it were, space would
  be ignored during comparisons any place in the string, when in fact, is
  it is only trailing space that char() ignores, e.g.:
 
 No, you don't understand.  The standard defines PAD SPACE as making
 *trailing* spaces irrelevant to comparisons, not embedded or leading
 spaces.  And they regard PAD SPACE as an attribute of a collation rather
 than of the string datatype.  This seems pretty wacko to me (in
 particular it's hard to see how NO PAD is useful with char(N) storage)
 but that's what they did.

So you can have two collating sequences where in one trailing space is
significant, and another that isn't?  Strange.

  What additional documentation is needed?
 
 Some specific discussion of the relationship to the standard would be
 helpful, perhaps.

I guess, but explaining it seems pretty complex in itself, and I am
unsure what value it adds.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Andrew Dunstan]

Bruce Momjian wrote:


What additional documentation is needed?
 


Some specific discussion of the relationship to the standard would be
helpful, perhaps.
   



I guess, but explaining it seems pretty complex in itself, and I am
unsure what value it adds.

 



It will give us something to keep the language lawyers occupied :-)

cheers

andrew

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Wilkin, Kurt]

Dann Corbit wrote:
 -Original Message-
 From: Tom Lane [mailto:[EMAIL PROTECTED]
 Sent: Thursday, October 20, 2005 2:54 PM
 To: Dann Corbit
 Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-
 [EMAIL PROTECTED] Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 Dann Corbit [EMAIL PROTECTED] writes:
 I guess that additional ambiguity arises if you add additional
 spaces to the end.  Many database systems solve this by trimming
 the characters from the end of the string upon storage and the
 returned 
 string will
 not
 have any trailing blanks.
 
 Can you document that?  ISTM that that would effectively make char(n)
 and varchar(n) exactly equivalent, which is ... um ... a bit stupid.
 
 This is SQL*Server:
 
 drop table test_char
 go
 create table test_char(
   fixed_30 char(30),
   varch_30 varchar(30),
   nchar_30  nchar(30),
   nvarc_30 nvarchar(30)
 )
 go
 insert into test_char values('Dann ', 'Dann ', 'Dann ', 'Dann ') go
 select len(fixed_30), len(varch_30), len(nchar_30), len(nvarc_30)
 from test_char go
 
 Result set:
 4 4   4   4

For SQL Server, you are seeing the behaviour of the len function, 
none of the data has been trimmed. 
Using the same tables:

select len('x' + (fixed_30) + 'x'), 
len('x' + varch_30 + 'x'), 
len('x' + nchar_30 + 'x'), 
len('x' + nvarc_30 + 'x') 
from test_char

Result set :

32  7   32  7




Cheers, Kurt.

-- 
This electronic message together with any attachments is confidential and
intended for the named recipient's use only.  If you are not the intended
recipient (i) do not copy, disclose or use the contents in any way, (ii)
please let us know by return email immediately then destroy the message, and
any hard copies of the message, and any attachments.  The sender of this
message is not responsible for any changes made to this message and/or any
attachments and/or connection linkages to the Internet referred to in this
message after it has been sent.  Unless otherwise stated, any pricing
information given in this message and/or attachments is indicative only, is
subject to change and does not constitute an offer to buy or sell securities
or derivatives at any price quoted.  Any reference to the terms of executed
transactions should be treated as preliminary only and subject to separate
formal written notification.  Where reference is made to research material
and/or research recommendations, the basis of the provision of such research
material and/or recommendations is set out in the relevant disclaimer.

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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Richard_D_Levine]

Tom Lane [EMAIL PROTECTED] wrote on 10/20/2005 03:11:23 PM:
snip
 The hard part would be in figuring out how
 the output routine could know how many spaces to add back.

The length is in the metadata for the column, or am I being dense?


  regards, tom lane


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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Lincoln Yeoh]

At 05:33 PM 10/19/2005 -0700, Dann Corbit wrote:

If there is a significant performance benefit to not expanding text 
columns in comparison operations, then it seems it should be OK.


I probably read the standard wrong, but it seems to me that varchar, char, 
and bpchar columns should all behave the same (e.g. if you do not expand 
with blank or the PAD character (whatever that is) then all char type 
columns should behave the same.  I guess that there could be different 
default collations for different column


I am not a DB guru. BUT IMO they should NOT behave the same.

Varchars should NOT be padded.

For the very reason when you select text out of varchar fields the result 
is not padded.


If I insert a string with a single trailing space into a varchar, I _want_ 
that single trailing space to still be there when I retrieve it, and not 
followed by more spaces. Otherwise I will have to pick a different database ;).


So similarly, I would expect that varchars 'a ' and 'a' when compared 
should be different.


However, in the case of _chars_ which are padded, then 'a ' should be 
padded so that it can be compared with 'a '.


Otherwise there will be no reason to do equality comparisons of char(5) 
fields with char(8) fields - they can NEVER be the same :).


But would that mean that when one does equality comparisons of varchars 
with chars, one would probably want padding? Or only varchars of the same 
length as the char would have a chance of matching?


Hmm.. I think I better leave this one to the DB gurus :). But I really 
don't ever want 'a ' to be the same as 'a   ' for varchars.


Link.


---(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] [HACKERS] 'a' == 'a '

[Richard_D_Levine]

I will happily reiterate that I am the troll who started this mess by
whining about how *Oracle* handles this.  Tom's explanation that CHAR is
has a PAD collation and VARCHAR has a NO PAD collation have restored my
faith that there is goodness in the world.  My whining was out of
ignorance.  I wouldn't change the proper way PostgreSQL works.  Documenting
it is good.  I will use this new found knowledge from now on in my database
designs.

Cheers,

Rick

Chris Travers [EMAIL PROTECTED] wrote on 10/20/2005 01:52:36 PM:

 Dann Corbit wrote:

 Let me make something clear:
 When we are talking about padding here it is only in the context of a
 comparison operator and NOT having anything to do with storage.
 
 
 IIrc, varchar and bpchar are stored in a similar way, but are presented
 differently when retrieved.  I.e. storage is separate from presentation
 in this case.  I.e. the padding in bpchar occurs when it is presented
 and stripped when it is stored.

 Again, I am happy solving this simply by documenting it since any
 questions of interpretation and implimentation of the standard would be
 answered.  So far what I (and I am sure others) have not heard is a
 strong case for changing the behavior, given that it is in line with a
 reasonable interpretation of the standards.

 Given two strings of different in a comparison, most database systems
 (by default) will blank pad the shorter string so that they are the same
 length before performing the comparison.
 
 
 Understood, but what gain do you have in a case like this that might
 justify the effort that would go into making it, say, an initdb option?
 How often does this behavior cause problems?

 Best Wishes,
 Chris Travers
 Metatron Technology Consulting


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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[John D. Burger]

[Removed all the non-list addresses]

Dann Corbit wrote:


Let me make something clear:
When we are talking about padding here it is only in the context of a
comparison operator and NOT having anything to do with storage.

Given two strings of different in a comparison, most database systems
(by default) will blank pad the shorter string so that they are the 
same

length before performing the comparison.

Hence, you will see that 'Danniel' = 'Danniel ' is true in most cases.

Now, this really does not have any connection with storage or varchar 
or

bpchar or char or text or anything like that.


Is this really true??? My understanding of the spec was that this was 
=exactly= the difference between char(N) and varchar(N) - the former is 
padded to length N when you store it, or at least the DB has to act as 
if this is the case.  Can someone quote the appropriate chapter and 
verse?


Thanks.

- John D. Burger
  MITRE


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

  http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Richard_D_Levine]

Dann Corbit [EMAIL PROTECTED] wrote on 10/20/2005 04:24:26 PM:

  -Original Message-
  From: [EMAIL PROTECTED]
 [mailto:[EMAIL PROTECTED]
  Sent: Thursday, October 20, 2005 2:12 PM
  To: Tom Lane
  Cc: Chris Travers; Dann Corbit; Greg Stark; josh@agliodbs.com; pgsql-
  [EMAIL PROTECTED]; pgsql-hackers@postgresql.org; Marc G.
 Fournier;
  Stephan Szabo; Terry Fielder; Tino Wildenhain
  Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 
 
  Tom Lane [EMAIL PROTECTED] wrote on 10/20/2005 03:11:23 PM:
  snip
   The hard part would be in figuring out how
   the output routine could know how many spaces to add back.
 
  The length is in the metadata for the column, or am I being dense?

 I guess that what Tom is saying is that it would be nice to store
 everything as VARCHAR.

snip

I get that part, but he asked how to determine the proper output length
based on the PAD semantics, and I was saying to just pad whatever is stored
to the length available in the column metadata for those collations that
require padding.

 I guess that additional ambiguity arises if you add additional spaces to
 the end.  Many database systems solve this by trimming the characters
 from the end of the string upon storage and the returned string will not
 have any trailing blanks.  I am not arguing pro nor con this way of
 doing things.

For PAD correlations, Oracle stores the blanks.  Interbase does not.  Going
from Interbase to Oracle I switched from CHAR to VARCHAR2 because of this,
shot my self in the foot by not understanding PAD, whined about it, and you
know the rest.

As to how it is output, I know the language interface has an effect.
Embedded SQL in C will put the null terminator in different places for
different databases and different switch settings particular to each
database.


---(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] [HACKERS] 'a' == 'a '

[Kevin Grittner]

I wonder how widespread the MicroSoft behavior is  Sybase ASE,
for example, gives this result set:

30  5   30  5  

That seems more appropriate to me.

-Kevin


 Dann Corbit [EMAIL PROTECTED] writes:
  I guess that additional ambiguity arises if you add additional
spaces to
  the end.  Many database systems solve this by trimming the
characters
  from the end of the string upon storage and the returned string will
not
  have any trailing blanks.
 
 Can you document that?  ISTM that that would effectively make char(n)
 and varchar(n) exactly equivalent, which is ... um ... a bit stupid.

This is SQL*Server:

drop table test_char
go
create table test_char(
  fixed_30 char(30),
  varch_30 varchar(30),
  nchar_30  nchar(30),
  nvarc_30 nvarchar(30)
)
go
insert into test_char values('Dann ', 'Dann ', 'Dann ', 'Dann ')
go
select len(fixed_30), len(varch_30), len(nchar_30), len(nvarc_30) 
from test_char
go

Result set:
4   4   4   4


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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Greg Stark]

Tom Lane [EMAIL PROTECTED] writes:

 Chris Travers [EMAIL PROTECTED] writes:
  If I understand the spec correctly, it seems to indicate that this is 
  specific to the locale/character set.
 
 The spec associates padding behavior with collations, which per spec are
 separate from the datatypes --- that is, you should be able to able to
 specify a collation for each string-type table column (whether char(N)
 or varchar(N)) and even for each literal string constant.  We do not
 currently have that capability, and accordingly fall back to binding
 PAD SPACE behavior to char(N) and NO PAD behavior to varchar(N).
 
 AFAICS this choice is allowed by the spec since the default collation is
 implementation-defined.

Does it even make sense for char(N) to not be space padded? I had the
impression char(N) was always N characters long, not more or less. I can't
picture any other character being used for padding, then you would need a more
flexible rtrim function.

And I can understand the collation order determining whether 'a' and 'a '
compare equal. But surely if you store 'a' in a varchar(N) you have to get 'a'
back out, not some other string! Does the spec really allow varchar to
actually be padded and not just compare ignoring trailing space?


(I can't believe anyone really wants varchar to be space padded. Space padding
always seemed like a legacy feature for databases with fixed record length
data types. Why would anyone want a string data type that can't represent all
strings?)

-- 
greg


---(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] [HACKERS] 'a' == 'a '

[Jan Wieck]

On 10/20/2005 2:17 AM, Greg Stark wrote:


(I can't believe anyone really wants varchar to be space padded. Space padding
always seemed like a legacy feature for databases with fixed record length
data types. Why would anyone want a string data type that can't represent all
strings?)


They must have buried that bow to COBOL so deep in the code that they 
had no choice but to abuse their power and stuff this cruft into the 
standard.



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 5: don't forget to increase your free space map settings

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
 [EMAIL PROTECTED] On Behalf Of Greg Stark
 Sent: Wednesday, October 19, 2005 11:17 PM
 To: Tom Lane
 Cc: Chris Travers; josh@agliodbs.com; pgsql-hackers@postgresql.org;
Dann
 Corbit; Stephan Szabo; Terry Fielder; Tino Wildenhain; Marc G.
Fournier;
 [EMAIL PROTECTED]; pgsql-general@postgresql.org
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 Tom Lane [EMAIL PROTECTED] writes:
 
  Chris Travers [EMAIL PROTECTED] writes:
   If I understand the spec correctly, it seems to indicate that this
is
   specific to the locale/character set.
 
  The spec associates padding behavior with collations, which per spec
are
  separate from the datatypes --- that is, you should be able to able
to
  specify a collation for each string-type table column (whether
char(N)
  or varchar(N)) and even for each literal string constant.  We do not
  currently have that capability, and accordingly fall back to binding
  PAD SPACE behavior to char(N) and NO PAD behavior to varchar(N).
 
  AFAICS this choice is allowed by the spec since the default
collation is
  implementation-defined.
 
 Does it even make sense for char(N) to not be space padded? I had the
 impression char(N) was always N characters long, not more or less. I
can't
 picture any other character being used for padding, then you would
need a
 more
 flexible rtrim function.
 
 And I can understand the collation order determining whether 'a' and
'a '
 compare equal. But surely if you store 'a' in a varchar(N) you have to
get
 'a'
 back out, not some other string! Does the spec really allow varchar to
 actually be padded and not just compare ignoring trailing space?
 
 
 (I can't believe anyone really wants varchar to be space padded. Space
 padding
 always seemed like a legacy feature for databases with fixed record
length
 data types. Why would anyone want a string data type that can't
represent
 all
 strings?)

Let me make something clear:
When we are talking about padding here it is only in the context of a
comparison operator and NOT having anything to do with storage.

Given two strings of different in a comparison, most database systems
(by default) will blank pad the shorter string so that they are the same
length before performing the comparison.

Hence, you will see that 'Danniel' = 'Danniel ' is true in most cases.

Now, this really does not have any connection with storage or varchar or
bpchar or char or text or anything like that.

It is only the action to be taken when a comparison operation is
performed.

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Chris Travers]

Dann Corbit wrote:


Let me make something clear:
When we are talking about padding here it is only in the context of a
comparison operator and NOT having anything to do with storage.
 

IIrc, varchar and bpchar are stored in a similar way, but are presented 
differently when retrieved.  I.e. storage is separate from presentation 
in this case.  I.e. the padding in bpchar occurs when it is presented 
and stripped when it is stored.


Again, I am happy solving this simply by documenting it since any 
questions of interpretation and implimentation of the standard would be 
answered.  So far what I (and I am sure others) have not heard is a 
strong case for changing the behavior, given that it is in line with a 
reasonable interpretation of the standards.



Given two strings of different in a comparison, most database systems
(by default) will blank pad the shorter string so that they are the same
length before performing the comparison.
 

Understood, but what gain do you have in a case like this that might 
justify the effort that would go into making it, say, an initdb option?  
How often does this behavior cause problems?


Best Wishes,
Chris Travers
Metatron Technology Consulting

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

 -Original Message-
 From: Chris Travers [mailto:[EMAIL PROTECTED]
 Sent: Thursday, October 20, 2005 11:53 AM
 To: Dann Corbit
 Cc: Greg Stark; Tom Lane; Chris Travers; josh@agliodbs.com; pgsql-
 [EMAIL PROTECTED]; Stephan Szabo; Terry Fielder; Tino Wildenhain;
 Marc G. Fournier; [EMAIL PROTECTED]; pgsql-
 [EMAIL PROTECTED]
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 Dann Corbit wrote:
 
 Let me make something clear:
 When we are talking about padding here it is only in the context of a
 comparison operator and NOT having anything to do with storage.
 
 
 IIrc, varchar and bpchar are stored in a similar way, but are
presented
 differently when retrieved.  I.e. storage is separate from
presentation
 in this case.  I.e. the padding in bpchar occurs when it is presented
 and stripped when it is stored.
 
 Again, I am happy solving this simply by documenting it since any
 questions of interpretation and implimentation of the standard would
be
 answered.  So far what I (and I am sure others) have not heard is a
 strong case for changing the behavior, given that it is in line with a
 reasonable interpretation of the standards.

I believe that this is a reasonable response.  In order to comply with
the standard, bpchar and varchar would have to be stored with different
default collating sequences (which is fine with me).  If (indeed) that
is the case, the only action needed would be to document the collating
sequences used.
 
 Given two strings of different in a comparison, most database systems
 (by default) will blank pad the shorter string so that they are the
same
 length before performing the comparison.
 
 
 Understood, but what gain do you have in a case like this that might
 justify the effort that would go into making it, say, an initdb
option?
 How often does this behavior cause problems?

I do not even know if it is a good idea.  I was just pointing out that
the behavior of PostgreSQL is different from all the big database
vendors in this area and according to my reading of the standard, the
behavior was not compliant.

As to how often it causes a problem, I can't say.  It has caused me
puzzlement on a few occasions, but no end of the world disasters.

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

Look back in the stack and you will find that I have quoted chapter and
verse (see the attached html file in a previous email that I sent).

This is in relation to the comparison operator.

 -Original Message-
 From: John D. Burger [mailto:[EMAIL PROTECTED]
 Sent: Thursday, October 20, 2005 12:53 PM
 To: Dann Corbit
 Cc: pgsql-hackers@postgresql.org; pgsql-general General
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 [Removed all the non-list addresses]
 
 Dann Corbit wrote:
 
  Let me make something clear:
  When we are talking about padding here it is only in the context of
a
  comparison operator and NOT having anything to do with storage.
 
  Given two strings of different in a comparison, most database
systems
  (by default) will blank pad the shorter string so that they are the
  same
  length before performing the comparison.
 
  Hence, you will see that 'Danniel' = 'Danniel ' is true in most
cases.
 
  Now, this really does not have any connection with storage or
varchar
  or
  bpchar or char or text or anything like that.
 
 Is this really true??? My understanding of the spec was that this was
 =exactly= the difference between char(N) and varchar(N) - the former
is
 padded to length N when you store it, or at least the DB has to act as
 if this is the case.  Can someone quote the appropriate chapter and
 verse?

Again, the operations that I was talking about literally have nothing to
do with the internal data type of the string, as long as it is
fundamentally a character type.  I was referring to what happens when
two strings are involved in a comparison operation.  The standard spells
it out very clearly.  Whether something is blank padded or not during a
comparison has nothing whatever to do with the type of the string and
everything to do with the collating sequence.

 Thanks.
 
 - John D. Burger
MITRE


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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Tom Lane]

Chris Travers [EMAIL PROTECTED] writes:
 IIrc, varchar and bpchar are stored in a similar way, but are presented 
 differently when retrieved.  I.e. storage is separate from presentation 
 in this case.  I.e. the padding in bpchar occurs when it is presented 
 and stripped when it is stored.

This is not so, although I've occasionally wondered whether we shouldn't
try to make it so.  Certainly we don't rely on char(N) to be physically
fixed-width (and can't, at least not with variable-width character
encodings) so there can be no performance advantage to actually storing
the insignificant spaces.  The hard part would be in figuring out how
the output routine could know how many spaces to add back.

regards, tom lane

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Dann Corbit]

 -Original Message-
 From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
 Sent: Thursday, October 20, 2005 2:12 PM
 To: Tom Lane
 Cc: Chris Travers; Dann Corbit; Greg Stark; josh@agliodbs.com; pgsql-
 [EMAIL PROTECTED]; pgsql-hackers@postgresql.org; Marc G.
Fournier;
 Stephan Szabo; Terry Fielder; Tino Wildenhain
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 
 
 Tom Lane [EMAIL PROTECTED] wrote on 10/20/2005 03:11:23 PM:
 snip
  The hard part would be in figuring out how
  the output routine could know how many spaces to add back.
 
 The length is in the metadata for the column, or am I being dense?

I guess that what Tom is saying is that it would be nice to store
everything as VARCHAR.  But with (for instance) BPCHAR, the returned
string is blank padded.  So if you store 
'Danniel' 
in BPCHAR(20), you will get back:
'Danniel '
But if you store 
'Danniel'
In VARCHAR(20)
You will get back exactly what you put in.

I guess that additional ambiguity arises if you add additional spaces to
the end.  Many database systems solve this by trimming the characters
from the end of the string upon storage and the returned string will not
have any trailing blanks.  I am not arguing pro nor con this way of
doing things.

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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Tom Lane]

[EMAIL PROTECTED] writes:
 Tom Lane [EMAIL PROTECTED] wrote on 10/20/2005 03:11:23 PM:
 The hard part would be in figuring out how
 the output routine could know how many spaces to add back.

 The length is in the metadata for the column, or am I being dense?

The output routine hasn't got access to that metadata.  In general,
in fact, a random Datum can't be linked to any column since it may have
been generated on-the-fly.

It'd be easy if we stuck the column width into the stored representation
of bpchar, but this would defeat one of the purposes, ie, to make the
stored representations of text and bpchar alike.

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] [HACKERS] 'a' == 'a '

[Tom Lane]

Dann Corbit [EMAIL PROTECTED] writes:
 I guess that additional ambiguity arises if you add additional spaces to
 the end.  Many database systems solve this by trimming the characters
 from the end of the string upon storage and the returned string will not
 have any trailing blanks.

Can you document that?  ISTM that that would effectively make char(n)
and varchar(n) exactly equivalent, which is ... um ... a bit stupid.

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] [HACKERS] 'a' == 'a '

[Dann Corbit]

 -Original Message-
 From: Tom Lane [mailto:[EMAIL PROTECTED]
 Sent: Thursday, October 20, 2005 2:54 PM
 To: Dann Corbit
 Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-
 [EMAIL PROTECTED]
 Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '
 
 Dann Corbit [EMAIL PROTECTED] writes:
  I guess that additional ambiguity arises if you add additional
spaces to
  the end.  Many database systems solve this by trimming the
characters
  from the end of the string upon storage and the returned string will
not
  have any trailing blanks.
 
 Can you document that?  ISTM that that would effectively make char(n)
 and varchar(n) exactly equivalent, which is ... um ... a bit stupid.

This is SQL*Server:

drop table test_char
go
create table test_char(
  fixed_30 char(30),
  varch_30 varchar(30),
  nchar_30  nchar(30),
  nvarc_30 nvarchar(30)
)
go
insert into test_char values('Dann ', 'Dann ', 'Dann ', 'Dann ')
go
select len(fixed_30), len(varch_30), len(nchar_30), len(nvarc_30) 
from test_char
go

Result set:
4   4   4   4


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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Andrew Dunstan]

Dann Corbit wrote:


-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 20, 2005 2:54 PM
To: Dann Corbit
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-
[EMAIL PROTECTED]
Subject: Re: [GENERAL] [HACKERS] 'a' == 'a '

Dann Corbit [EMAIL PROTECTED] writes:
   


I guess that additional ambiguity arises if you add additional
 


spaces to
 


the end.  Many database systems solve this by trimming the
 


characters
 


from the end of the string upon storage and the returned string will
 


not
 


have any trailing blanks.
 


Can you document that?  ISTM that that would effectively make char(n)
and varchar(n) exactly equivalent, which is ... um ... a bit stupid.
   



This is SQL*Server:

drop table test_char
go
create table test_char(
 fixed_30 char(30),
 varch_30 varchar(30),
 nchar_30  nchar(30),
 nvarc_30 nvarchar(30)
)
go
insert into test_char values('Dann ', 'Dann ', 'Dann ', 'Dann ')
go
select len(fixed_30), len(varch_30), len(nchar_30), len(nvarc_30) 
from test_char

go

Result set:
4   4   4   4


 



Yech.

What does one do in such a system if you want trailing blanks to be 
significant, or even kept?


Anyway, the consensus seems to be that Postgresql's behavious is 
consistent with a reasonable reading of the standard, so is there 
anything really left to discuss, other than a possible addition to 
documentation?


cheers

andrew



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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Chris Travers]

Josh Berkus wrote:


Dann,

 


I think that whatever is done ought to be whatever the standard says.
If I misinterpret the standard and PostgreSQL is doing it right, then
that is fine.  It is just that PostgreSQL is very counter-intuitive
compared to other database systems that I have used in this one
particular area.  When I read the standard, it looked to me like
PostgreSQL was not performing correctly.  It is not unlikely that I read
it wrong.
   



AFAIT, the standard says implementation-specific.   So we're standard.

The main cost for comparing trimmed values is performance; factoring an 
rtrim into every comparison will add significant overhead to the already 
CPU-locked process of, for example, creating indexes.  We're looking for 
ways to make the comparison operators lighter-weight, not heavier.
 

If I understand the spec correctly, it seems to indicate that this is 
specific to the locale/character set.  Assuming that the standard 
doesn't have anything to do with any character sets, it should be 
possible to make this available for those who want it as an initdb 
option.  Whether or not this is important enough to offer or not is 
another matter.


Personally my questions are:

1)  How many people have been bitten by this badly?
2)  How many people have been bitten by joins that depend on padding?

Personally, unlike case folding, this seems to be an area where a bit of 
documentation (i.e. all collation sets have are assumed to have the NO 
PAD option in the SQL standard) would be sufficient to answer to 
questions of standards-compliance.


My general perspective on this is that if trailing blanks are a significant 
hazard for your application, then trim them on data input.  That requires 
a *lot* less peformance overhead than doing it every time you compare 
something.  
 

In general I agree.  But I am not willing to jump to the conclusion that 
it will never be warranted to add this as an initdb option.  I am more 
interested in what cases people see where this would be required.  But I 
agree that the bar is much higher than it is in many other cases.


Best Wishes,
Chris Travers
Metatron Technology Consulting

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

Re: [GENERAL] [HACKERS] 'a' == 'a '

[Tom Lane]

Chris Travers [EMAIL PROTECTED] writes:
 If I understand the spec correctly, it seems to indicate that this is 
 specific to the locale/character set.

The spec associates padding behavior with collations, which per spec are
separate from the datatypes --- that is, you should be able to able to
specify a collation for each string-type table column (whether char(N)
or varchar(N)) and even for each literal string constant.  We do not
currently have that capability, and accordingly fall back to binding
PAD SPACE behavior to char(N) and NO PAD behavior to varchar(N).

AFAICS this choice is allowed by the spec since the default collation is
implementation-defined.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] Avoiding io penalty when updating large objects

[Tom Lane]

Alvaro Herrera [EMAIL PROTECTED] writes:
 On Tue, Jun 28, 2005 at 07:38:43PM -0700, Mark Dilger wrote:
 If, for a given row, the value of c is, say, approximately 2^30 bytes 
 large, then I would expect it to be divided up into 8K chunks in an 
 external table, and I should be able to fetch individual chunks of that 
 object (by offset) rather than having to detoast the whole thing.

 I don't think you can do this with the TOAST mechanism.  The problem is
 that there's no API which allows you to operate on only certain chunks
 of data.

There is the ability to fetch chunks of a toasted value (if it was
stored out-of-line but not compressed).  There is no ability at the
moment to update it by chunks.  If Mark needs the latter then large
objects are probably the best bet.

I'm not sure what it'd take to support chunkwise update of toasted
fields.  Jan, any thoughts?

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] Avoiding io penalty when updating large objects

[Mark Dilger]

Tom Lane wrote:

Alvaro Herrera [EMAIL PROTECTED] writes:


On Tue, Jun 28, 2005 at 07:38:43PM -0700, Mark Dilger wrote:

If, for a given row, the value of c is, say, approximately 2^30 bytes 
large, then I would expect it to be divided up into 8K chunks in an 
external table, and I should be able to fetch individual chunks of that 
object (by offset) rather than having to detoast the whole thing.




I don't think you can do this with the TOAST mechanism.  The problem is
that there's no API which allows you to operate on only certain chunks
of data.



There is the ability to fetch chunks of a toasted value (if it was
stored out-of-line but not compressed).  There is no ability at the
moment to update it by chunks.  If Mark needs the latter then large
objects are probably the best bet.

I'm not sure what it'd take to support chunkwise update of toasted
fields.  Jan, any thoughts?

regards, tom lane

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


Ok,

If there appears to be a sane path to implementing this, I may be able to 
contribute engineering effort to it.  (I manage a group of engineers and could 
spare perhaps half a man year towards this.)  But I would like direction as to 
how you all think this should be done, or whether it is just a bad idea.


I can also go with the large object approach.  I'll look into that.

Mark Dilger

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

  http://archives.postgresql.org

Re: [GENERAL] [HACKERS] [PATCHES] Removing Kerberos 4

[Jim C. Nasby]

On Thu, Jun 23, 2005 at 07:34:30PM +0200, Magnus Hagander wrote:
  Has Kerb4 been marked as depricated in the docs at all? If 
  not it might be best to just do that and then yank it later.
 
 Yes, since 7.4.
 
 http://www.postgresql.org/docs/8.0/static/auth-methods.html#KERBEROS-AUT
 H
 http://www.postgresql.org/docs/7.4/static/auth-methods.html#KERBEROS-AUT
 H 
 
 Kerberos 4 is considered insecure and no longer recommended for general
 use.

Just as a nitpick, in the future it would probably be better to
explicitely say if something is considered depricated and will be
removed in the future. Having said that, that statement means it's
removal shouldn't come as a shock to anyone.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: Where do you want to go today?
Linux: Where do you want to go tomorrow?
FreeBSD: Are you guys coming, or what?

---(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] [HACKERS] [PATCHES] Removing Kerberos 4

[Jim C. Nasby]

On Wed, Jun 22, 2005 at 04:39:15PM -0400, Tom Lane wrote:
 Magnus Hagander [EMAIL PROTECTED] writes:
  Yeah. But it has been declared dead by the Kerberos folks
  (http://www.faqs.org/faqs/kerberos-faq/general/section-7.html. And this
  document is from 2000, an dit was declared already then)...
 
 Right.  The real question here is who's going to be using a 2005
 database release with a pre-2000 security system?  There's a fair
 amount of code there and no evidence that time spent on testing
 and maintaining it is going to benefit anyone anymore.
 
 If someone wakes up and says hey, I'm still ACTUALLY using that code,
 I'm willing to forbear ... but otherwise I think its time is long gone.

While I agree, if it's easy to just disable kerb without actually
ripping the code out right now that might be a tad 'safer', as there
might be some users who are using it but don't read the mailling lists.

Has Kerb4 been marked as depricated in the docs at all? If not it might
be best to just do that and then yank it later.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: Where do you want to go today?
Linux: Where do you want to go tomorrow?
FreeBSD: Are you guys coming, or what?

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

Re: [GENERAL] [HACKERS] [PATCHES] Removing Kerberos 4

[Magnus Hagander]

 Has Kerb4 been marked as depricated in the docs at all? If 
 not it might be best to just do that and then yank it later.

Yes, since 7.4.

http://www.postgresql.org/docs/8.0/static/auth-methods.html#KERBEROS-AUT
H
http://www.postgresql.org/docs/7.4/static/auth-methods.html#KERBEROS-AUT
H 

Kerberos 4 is considered insecure and no longer recommended for general
use.


//Magnus

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

Re: [GENERAL] [HACKERS] mirroring oracle database in pgsql

[Jonah H. Harris]

I wouldn't say it's enterprise-grade, but one could probably make it work.

Sean Davis wrote:

There is DBI-link, but this probably isn't an enterprise solution

http://www.pervasive-postgres.com/postgresql/tidbits.asp

Sean

On Jun 13, 2005, at 2:31 PM, Jonah H. Harris wrote:

The contrib/dblink module only works for creating a database link to 
another PostgreSQL database.  I'm working on a dblink_ora which allows 
you to connect to an 8i, 9i, or 10g system the same way.  dblink_ora 
is based on dblink, not dblink_tds (for SQL Server) so it has more 
features.  Also, I'm using the Oracle Instant Client libraries/SDK, so 
you don't need to do the whole Oracle Client install to use dblink_ora.


I'm currently doing some alpha testing on it but if you would like to 
use it in beta, let me know.  Also, if anyone has *a lot* of 
experience with OCI, I'd like to talk about a couple things.


-Jonah


Christopher Kings-Lynne wrote:


Check out EnterprisDB: www.enterprisedb.com
Chris
Edward Peschko wrote:


hey all,


I'm trying to convince some people here to adopt either mysql or 
postgresql
as a relational database here.. However, we can't start from a clean 
slate; we have a very mature oracle database that applications point 
to right now, and so we need a migration path. I went to the mysql 
folks, and it looks
like its going to be quite a while before mysql is up to the task, 
so I thought I'd try pgsql.

Anyways, I was thinking of taking the following steps:


a) finding a Java API that transparently supports both 
postgresql and

Oracle data access and stored procedure calls.

b) instrumenting the Oracle database so that all tables support
timestamps on data rows.

c) mirroring the Oracle database in MySQL.

d) making interface code connecting the MySQL database to the
   Oracle database (and both applying updates to the database
   as well as data.

In other words, I'm looking to make a postgresql - Oracle mirroring 
tool, and syncing the databases on a nightly basis, and I was

wondering if anybody had experience with this sort of thing.

As I see it, if we pull this off we could save quite a bit in 
licensing costs - we'd still have oracle around, but it would only 
be a datastore for talking to other oracle databases, and run by 
batch, not accessed by end users.


However:

a) I'm not sure how well stored procs, views, triggers and
   indexes transfer over from oracle to postgresql.

b) I'm not sure how scalable postgresql is, and how well
   it handles multiprocessor support (we'd be using a
six-processor box.



As an aside, how much experience do people on the list have with
enterprise db? I was thinking that they might alleviate the 
mirroring headaches quite a bit, but they don't seem to have a 
solaris port.. Anybody have a take on their db?



Ed

(
 ps - if you subscribe to the mysql list, no you're not seeing double.
  I posted a very similar message on the mysql lists a couple
  of days ago.. )

---(end of 
broadcast)---

TIP 8: explain analyze is your friend


---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org



--
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI  | fax:   505.224.3014
525 Buena Vista SE   | [EMAIL PROTECTED]
Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/

A hacker on a roll may be able to produce, in a period of a few
months, something that a small development group (say, 7-8 people)
would have a hard time getting together over a year.  IBM used to
report that certain programmers might be as much as 100 times as
productive as other workers, or more.

-- Peter Seebach

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





--
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI  | fax:   505.224.3014
525 Buena Vista SE   | [EMAIL PROTECTED]
Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/

A hacker on a roll may be able to produce, in a period of a few
months, something that a small development group (say, 7-8 people)
would have a hard time getting together over a year.  IBM used to
report that certain programmers might be as much as 100 times as
productive as other workers, or more.

-- Peter Seebach

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

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

Re: [GENERAL] [HACKERS] mirroring oracle database in pgsql

[Sean Davis]

There is DBI-link, but this probably isn't an enterprise solution

http://www.pervasive-postgres.com/postgresql/tidbits.asp

Sean

On Jun 13, 2005, at 2:31 PM, Jonah H. Harris wrote:

The contrib/dblink module only works for creating a database link to 
another PostgreSQL database.  I'm working on a dblink_ora which allows 
you to connect to an 8i, 9i, or 10g system the same way.  dblink_ora 
is based on dblink, not dblink_tds (for SQL Server) so it has more 
features.  Also, I'm using the Oracle Instant Client libraries/SDK, so 
you don't need to do the whole Oracle Client install to use 
dblink_ora.


I'm currently doing some alpha testing on it but if you would like to 
use it in beta, let me know.  Also, if anyone has *a lot* of 
experience with OCI, I'd like to talk about a couple things.


-Jonah


Christopher Kings-Lynne wrote:

Check out EnterprisDB: www.enterprisedb.com
Chris
Edward Peschko wrote:

hey all,


I'm trying to convince some people here to adopt either mysql or 
postgresql
as a relational database here.. However, we can't start from a clean 
slate; we have a very mature oracle database that applications point 
to right now, and so we need a migration path. I went to the mysql 
folks, and it looks
like its going to be quite a while before mysql is up to the task, 
so I thought I'd try pgsql.

Anyways, I was thinking of taking the following steps:


a) finding a Java API that transparently supports both 
postgresql and

Oracle data access and stored procedure calls.

b) instrumenting the Oracle database so that all tables support
timestamps on data rows.

c) mirroring the Oracle database in MySQL.

d) making interface code connecting the MySQL database to the
   Oracle database (and both applying updates to the database
   as well as data.

In other words, I'm looking to make a postgresql - Oracle mirroring 
tool, and syncing the databases on a nightly basis, and I was

wondering if anybody had experience with this sort of thing.

As I see it, if we pull this off we could save quite a bit in 
licensing costs - we'd still have oracle around, but it would only 
be a datastore for talking to other oracle databases, and run by 
batch, not accessed by end users.


However:

a) I'm not sure how well stored procs, views, triggers and
   indexes transfer over from oracle to postgresql.

b) I'm not sure how scalable postgresql is, and how well
   it handles multiprocessor support (we'd be using a
six-processor box.



As an aside, how much experience do people on the list have with
enterprise db? I was thinking that they might alleviate the 
mirroring headaches quite a bit, but they don't seem to have a 
solaris port.. Anybody have a take on their db?



Ed

(
 ps - if you subscribe to the mysql list, no you're not seeing 
double.

  I posted a very similar message on the mysql lists a couple
  of days ago.. )

---(end of 
broadcast)---

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

TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


--
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI  | fax:   505.224.3014
525 Buena Vista SE   | [EMAIL PROTECTED]
Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/

A hacker on a roll may be able to produce, in a period of a few
months, something that a small development group (say, 7-8 people)
would have a hard time getting together over a year.  IBM used to
report that certain programmers might be as much as 100 times as
productive as other workers, or more.

-- Peter Seebach

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





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

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

Re: [GENERAL] [HACKERS] mirroring oracle database in pgsql

[Sean Davis]

On Jun 13, 2005, at 6:48 PM, Jonah H. Harris wrote:

I wouldn't say it's enterprise-grade, but one could probably make it 
work.




I totally agree--I use it relatively often.  This single piece of 
software opened my eyes as to the extent to which the procedure 
languages can be leveraged.


Sean


Sean Davis wrote:
There is DBI-link, but this probably isn't an enterprise 
solution

http://www.pervasive-postgres.com/postgresql/tidbits.asp
Sean



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

Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was

[Bruce Momjian]

Added to TODO:

* Prevent child tables from altering constraints like CHECK that were
  inherited from the parent table


---

Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  Doing anything to restrict dropping of inherited constraints seems like
  wasted effort and potentially annoying anyhow.
 
 Uh, why?  Arguably the constraints are as much part of the parent table
 definition as the columns themselves.  If you had check (f1  0) in
 the definition of a table, wouldn't you be pretty surprised to select
 from it and find rows with f1  0?
 
 regression=# create table parent(f1 int check (f1  0));
 CREATE TABLE
 regression=# create table child() inherits(parent);
 CREATE TABLE
 regression=# alter table child drop constraint parent_f1_check;
 ALTER TABLE
 regression=# insert into child values(-1);
 INSERT 0 1
 regression=# select * from parent;
  f1
 
  -1
 (1 row)
 
 I think a good argument can be made that the above behavior is a bug,
 and that the ALTER command should have been rejected.  We've gone to
 great lengths to make sure you can't ALTER a child table to make it
 incompatible with the parent in terms of the column names and types;
 shouldn't this be true of check constraints as well?
 
   regards, tom lane
 
 ---(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
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

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

Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 Doing anything to restrict dropping of inherited constraints seems like
 wasted effort and potentially annoying anyhow.

Uh, why?  Arguably the constraints are as much part of the parent table
definition as the columns themselves.  If you had check (f1  0) in
the definition of a table, wouldn't you be pretty surprised to select
from it and find rows with f1  0?

regression=# create table parent(f1 int check (f1  0));
CREATE TABLE
regression=# create table child() inherits(parent);
CREATE TABLE
regression=# alter table child drop constraint parent_f1_check;
ALTER TABLE
regression=# insert into child values(-1);
INSERT 0 1
regression=# select * from parent;
 f1

 -1
(1 row)

I think a good argument can be made that the above behavior is a bug,
and that the ALTER command should have been rejected.  We've gone to
great lengths to make sure you can't ALTER a child table to make it
incompatible with the parent in terms of the column names and types;
shouldn't this be true of check constraints as well?

regards, tom lane

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

Re: [GENERAL] [HACKERS] Inherited constraints and search paths

[Simon Riggs]

On Fri, 2005-05-20 at 11:51 -0400, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  Doing anything to restrict dropping of inherited constraints seems like
  wasted effort and potentially annoying anyhow.
 
 Uh, why?  Arguably the constraints are as much part of the parent table
 definition as the columns themselves.  If you had check (f1  0) in
 the definition of a table, wouldn't you be pretty surprised to select
 from it and find rows with f1  0?
 
 regression=# create table parent(f1 int check (f1  0));
 CREATE TABLE
 regression=# create table child() inherits(parent);
 CREATE TABLE
 regression=# alter table child drop constraint parent_f1_check;
 ALTER TABLE
 regression=# insert into child values(-1);
 INSERT 0 1
 regression=# select * from parent;
  f1
 
  -1
 (1 row)
 
 I think a good argument can be made that the above behavior is a bug,
 and that the ALTER command should have been rejected.  We've gone to
 great lengths to make sure you can't ALTER a child table to make it
 incompatible with the parent in terms of the column names and types;
 shouldn't this be true of check constraints as well?

Thats a good case. I retract my comment on potentially annoying.

If you were going to fix that by adding a column that allows me to tell
the difference between inherited and non-inherited relations, that would
be a very useful piece of info for partition elimination. In that case I
would also retract my comment on wasted effort. :-)

If you're looking for other inheritance wierdies, you may also be
interested in this one. When you create a table that inherits from a
master, it copies across constraints with exactly matching names. If a
constraint is then added to the master, the constraint is copied across
to the child but does not have the same name. So the name of inherited
constraints differs depending upon whether CREATE or ALTER puts them
there.

FWIW, fixing either of those won't get in my way on partitioning...

Best Regards, Simon Riggs


---(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] [HACKERS] Inherited constraints and search paths (was Re:

[Tom Lane]

Simon Riggs [EMAIL PROTECTED] writes:
 If you were going to fix that by adding a column that allows me to tell
 the difference between inherited and non-inherited relations, that would
 be a very useful piece of info for partition elimination.

Inherited and non-inherited constraints you mean?  Probably.  I hadn't
thought through the details, but certainly there would need to be some
better way of telling whether a constraint was inherited.

 If you're looking for other inheritance wierdies, you may also be
 interested in this one. When you create a table that inherits from a
 master, it copies across constraints with exactly matching names. If a
 constraint is then added to the master, the constraint is copied across
 to the child but does not have the same name.

Hmm, that's weird: if you give an explicit name (add constraint foo)
then it's used, but if you let the system generate the name it's not
the same.  I agree that was probably unintentional.  Does anyone want to
argue for keeping it this way?

regression=# create table parent(f1 int check (f1  0));
CREATE TABLE
regression=# create table child() inherits(parent);
CREATE TABLE
regression=# alter table parent add check (f1  100);
ALTER TABLE
regression=# \d parent
Table public.parent
 Column |  Type   | Modifiers
+-+---
 f1 | integer |
Check constraints:
parent_f1_check CHECK (f1  0)
parent_f1_check1 CHECK (f1  100)

regression=# \d child
 Table public.child
 Column |  Type   | Modifiers
+-+---
 f1 | integer |
Check constraints:
parent_f1_check CHECK (f1  0)
child_f1_check CHECK (f1  100)
Inherits: parent

(This is, btw, another case that would break the current code for
identifying inherited constraints in pg_dump.  Given a positive marker
for an inherited constraint, however, we wouldn't care.  So I don't
think we need to consider pg_dump in debating which behavior we like.)

regards, tom lane

---(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] [HACKERS] plPHP in core?

[Russell Smith]

On Tue, 5 Apr 2005 06:01 am, Joshua D. Drake wrote:
 Tom Lane wrote:
 
 Andrew Dunstan [EMAIL PROTECTED] writes:
 
 ... If there are no license or build issues I'm in favor.
 
 
 Peter has pointed out that the problem of circular dependencies is a
 showstopper for integrating plPHP.  The build order has to be
  Postgres
  PHP (since its existing DB support requires Postgres to build)
  plPHP
 so putting #1 and #3 into the same package is a no go.  Which is too
 bad, but I see no good way around it.
 
 O.k. I am confused here. You do not need PHP DB support for plPHP. You only
 need the php.so (once were done anyway). Which means that as long as PHP
 is installed it will work, just like plperl or plpython.
 
 The ONLY reason you would build PHP separately is if your stock installed
 PHP didn't have a feature enabled that you want. This has nothing at all
 to do with plPHP.
 
The issue also includes the fact that you can't install libpq without having 
postgresql
installed.  If you could do that, the circular dependency wouldn't exist.

Some systems build postgresql into php, given that is the case, what Tom says 
is correct.
First you would have to force postgresql to be installed without pl/php.  Then 
install php
with postgresql support, then install pl/php.

OR

Install php without postgresql support
Install postgresql with pl/php
Rebuild php with postgresql support (Unless you only want it available in the 
db)

I may be a bad man for suggesting it...  But is it possible to ship libpq as a 
seperate
tarball that you can compile without postgresql server?

Regards

Russell Smith

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

Re: [GENERAL] [HACKERS] plPHP in core?

[Martijn van Oosterhout]

On Tue, Apr 05, 2005 at 06:06:09PM +1000, Russell Smith wrote:
 The issue also includes the fact that you can't install libpq without having 
 postgresql
 installed.  If you could do that, the circular dependency wouldn't exist.
 
 Some systems build postgresql into php, given that is the case, what Tom says 
 is correct.
 First you would have to force postgresql to be installed without pl/php.  
 Then install php
 with postgresql support, then install pl/php.
 
 OR
 
 Install php without postgresql support
 Install postgresql with pl/php
 Rebuild php with postgresql support (Unless you only want it available in the 
 db)

Take for example Debian, it autobuilds any source package on 11
architectures or so. The rule is, install dependancies, build source.
It has to be reproducable. You can't build twice and get different
results. Yes, if you're building it yourself you can do all sorts of
trick, but autobuilders can't. Circular dependancies are a no-no.

 I may be a bad man for suggesting it...  But is it possible to ship libpq as 
 a seperate
 tarball that you can compile without postgresql server?

I guess that seperate tarball would have to include pg_dump, pg_ctl and
any of the other included programs that depend on libpq. Seperating
server and client portions is an interesting idea. Ofcourse, the
regression tests would become a third package and then you could spend
time making them all match.

I suppose the choice comes down to either PHP splitting the DB access
(like other languages) or PostgreSQL splitting out pl/PHP.
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpOGoSh5FUYe.pgp
Description: PGP signature

Re: [GENERAL] [HACKERS] plPHP in core?

[Andrew Dunstan]

Greg Sabino Mullane said:




 Other languages don't require PG to be installed in order to compile
 them.  For example, you can build Perl (with no Postgres on the
 system), build Postgres and then build DBD::Pg as a completely
 separate step.


 Just so we are all on the same sheet of music, DBD::Pg is a completely
 different animal from Pl/Perl


I think everybody gets that.

 and really has nothing to do with the
 discussion of adding Pl/PHP to the core.


It's relevant because it's the *client* side support in PHP that creates a
build dependency of PHP on Postgres. As was being pointed out above, Perl
doesn't suffer from this defect.

cheers

andrew



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

   http://archives.postgresql.org

Re: [GENERAL] [HACKERS] plPHP in core?

[Martín Marqués]

El Lun 04 Abr 2005 17:36, Tom Lane escribió:
 Joshua D. Drake [EMAIL PROTECTED] writes:
  Maybe I am just dense, but the argument seems to be completely moot. PHP 
  is no different than Perl or Python in this case.
 
 Perl and Python don't have BuildPrereq: postgresql-devel in their 
rpmspecs.
 PHP does.

The header files would not be a problem. The real problem is that you also 
need to have postgresql-libs. :-(

Any way, RH deals all the times with circular dependencies.

P.D.: It would be nice to have plPHP in the core, IMHO.

-- 
 09:03:26 up 3 days, 17:32,  1 user,  load average: 0.39, 0.61, 0.64
-
Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador
 Universidad Nacional
  del Litoral
-

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

Re: [GENERAL] [HACKERS] plPHP in core?

[Martín Marqués]

El Lun 04 Abr 2005 18:00, Doug McNaught escribió:
 Robert Treat [EMAIL PROTECTED] writes:
 
  If by stripped down you mean without postgresql database support then
  I'll grant you that, but it is no different than other any other pl
  whose parent language requires postgresql to be installed.  If packagers
  are able to handle those languages than why can't they do the same with
  PHP ?
 
 Other languages don't require PG to be installed in order to compile
 them.  For example, you can build Perl (with no Postgres on the
 system), build Postgres and then build DBD::Pg as a completely
 separate step.

The same thing can be done with PHP.

-- 
 09:25:38 up 3 days, 17:54,  1 user,  load average: 0.45, 0.28, 0.38
-
Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador
 Universidad Nacional
  del Litoral
-

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

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

Re: [GENERAL] [HACKERS] plPHP in core?

[Tom Lane]

=?iso-8859-1?q?Mart=EDn_Marqu=E9s?= martin@bugs.unl.edu.ar writes:
 El Lun 04 Abr 2005 17:36, Tom Lane escribió:
 Perl and Python don't have BuildPrereq: postgresql-devel in their rpmspecs.
 PHP does.

 The header files would not be a problem. The real problem is that you also 
 need to have postgresql-libs. :-(

Actually the header files are a problem too, because you can't have 'em
without doing at least a configure to generate the machine-specific ones.
The configure would already fail if PHP weren't installed and --with-php
were mentioned.  So the process would have to look something like
-- configure PG, but lie about your ultimate intentions
-- install bogus PG header files
-- build and install PHP
-- reconfigure PG, and hope you don't mess up by changing
   anything except the --with-php flag
-- build and install PG
This is just not reasonable from a packaging standpoint.

regards, tom lane

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