[GENERAL] GSSAPI authentication with AD

[Abbas]

Hi,

I am struggling to provide access to the windows Active Directory user into
postgres database.

Am using windows server 2008R2, created forest in AD with 2003
compatibility. postgresql 9.3.1 has been compiled with gssapi and krb5
support on centos both windows server and contos are on vms with static ips.

I walkthrough as follows;

created enterprisedb user in AD with DES encryption type.

Added centos host to AD domain.

C:\Users\Administrator>setspn -S POSTGRES/centos.my.testdomain.lan
my.testdomain.lan\enterprisedb

Checking domain DC=my,DC=testdomain,DC=lan

Registering ServicePrincipalNames for
CN=enterprisedb,CN=Users,DC=my,DC=testdomain,DC=lan

POSTGRES/centos.my.testdomain.lan

Updated object

C:\Users\Administrator>ktpass -out postgres.keytab -princ
POSTGRES/cen...@my.testdomain.lan -mapUser enterprisedb -pass XX
-crypto DES-CBC-MD5

Targeting domain controller: WIN-UC777GC73I8.my.testdomain.lan

Using legacy password setting method

Successfully mapped POSTGRES/centos to enterprisedb.

WARNING: pType and account type do not match. This might cause problems.

Key created.

Output keytab to postgres.keytab:

Keytab version: 0x502

keysize 60 POSTGRES/cen...@my.testdomain.lan ptype 0 (KRB5_NT_UNKNOWN) vno
8 etype 0x3 (DES-CBC-MD5) keylength 8 (0x1af1c29ebf252549)

-bash-4.1$ cat /etc/krb5.conf

[logging]

 default = FILE:/var/log/krb5libs.log

 kdc = FILE:/var/log/krb5kdc.log

 admin_server = FILE:/var/log/kadmind.log

[libdefaults]

 debug=true

 default_realm = MY.TESTDOMAIN.LAN

 allow_weak_crypto = 1

 dns_lookup_realm = false

 dns_lookup_kdc = false

 ticket_lifetime = 24h

 renew_lifetime = 7d

 forwardable = true

[realms]

 EXAMPLE.COM = {

  kdc = kerberos.example.com

  admin_server = kerberos.example.com

 }


 MYWIN.MY.TESTDOMAIN.LAN = {

  kdc = .my.testdomain.lan

 }

 MY.TESTDOMAIN.LAN = {

  kdc = win-uc777gc73i8.my.testdomain.lan

 }

[domain_realm]

 .example.com = EXAMPLE.COM

 example.com = EXAMPLE.COM

 mywin.my.testdomain.lan = MYWIN.MY.TESTDOMAIN.LAN

 .mywin.my.testdomain.lan = MYWIN.MY.TESTDOMAIN.LAN




out put klist:

-bash-4.1$ klist

Ticket cache: FILE:/tmp/krb5cc_501

Default principal: POSTGRES/cen...@my.testdomain.lan


Valid starting ExpiresService principal

11/25/13 00:41:34  11/25/13 10:41:38
krbtgt/my.testdomain@my.testdomain.lan

renew until 12/02/13 00:41:34

11/25/13 00:41:41  11/25/13 10:41:38  postgres/centos@

renew until 12/02/13 00:41:34

11/25/13 00:41:41  11/25/13 10:41:38  postgres/cen...@my.testdomain.lan

renew until 12/02/13 00:41:34


-bash-4.1$ kinit -V -k -t /opt/PostgreSQL/9.3.1/data/postgres.keytab
POSTGRES/cen...@my.testdomain.lan

Using default cache: /tmp/krb5cc_501

Using principal: POSTGRES/cen...@my.testdomain.lan

Using keytab: /opt/PostgreSQL/9.3.1/data/postgres.keytab

Authenticated to Kerberos v5

-bash-4.1$

-bash-4.1$ klist -k /opt/PostgreSQL/9.3.1/data/postgres.keytab

Keytab name: FILE:/opt/PostgreSQL/9.3.1/data/postgres.keytab

KVNO Principal


--

   8 POSTGRES/cen...@my.testdomain.lan

postgresql.conf:

# Kerberos and GSSAPI

krb_server_keyfile = '/opt/PostgreSQL/9.3.1/data/postgres.keytab'

krb_srvname = 'POSTGRES'# (Kerberos only)


pg_hba.conf:

hostall  all0.0.0.0/0 gss


while I was trying to connect psql client on centos I am getting below
error;


-bash-4.1$ ~/bin/psql -h centos.MY.TESTDOMAIN.LAN -U enterprisedb

psql: FATAL:  GSSAPI authentication failed for user "enterprisedb"

-bash-4.1$

pglogs :

LOG:  provided user name (enterprisedb) and authenticated user name
(POSTGRES/centos) do not match

FATAL:  GSSAPI authentication failed for user "enterprisedb"

DETAIL:  Connection matched pg_hba.conf line 86: "hostall
all0.0.0.0/0 gss"


Log output of /tmp/krb5_gp.log

[3947] 1385322573.406775: ccselect can't find appropriate cache for server
principal postgres/centos@

[3947] 1385322573.406955: Getting credentials
POSTGRES/cen...@my.testdomain.lan -> postgres/centos@ using ccache
FILE:/tmp/krb5cc_501

[3947] 1385322573.407067: Retrieving POSTGRES/cen...@my.testdomain.lan ->
postgres/centos@ from FILE:/tmp/krb5cc_501 with result: 0/Unknown code 0

[3947] 1385322573.407203: Creating authenticator for
POSTGRES/cen...@my.testdomain.lan -> postgres/centos@, seqnum 222559749,
subkey des-cbc-crc/7B63, session key des-cbc-crc/66D8

[3947] 1385322573.407227: Negotiating for enctypes in authenticator:
aes256-cts, aes128-cts, des3-cbc-sha1, rc4-hmac, des-cbc-crc, des,
des-cbc-md4

[3947] 1385322573.418759: ccselect can't find appropriate cache for server
principal postgres/centos@

[3947] 1385322573.418896: Read AP-REP, time 1385322576.407247, subkey
aes256-cts/710D, seqnum 574336718


Please suggest my where am missing.
Thanks in advance.
Abbas

[GENERAL] Fwd: FW: Really heart touching.........

[Abbas]

Best Regards,
Abbas


On Wed, Apr 25, 2012 at 11:16 AM, Akhila Banu Rumi <
akhilabanu_r...@infosys.com> wrote:

>  Really Heart touching … 
>
> ** **
>
> *From:* Amey Ratnakar Prabhu
> *Posted At:* Wednesday, April 25, 2012 9:16 AM
> *Posted To:* HYD General
> *Conversation:* Really heart touching.
> *Subject:* Really heart touching.
>
> ** **
>
> My friend , unable to stop his tears, has confined himself to the restroom
> and we are able to hear him cry loudly.
>
> Really full of sentiments and emotions. Early morning touch chesindi raaa*
> ***
>
> ** **
>
> *From:* Avinash Peravali
> *Posted At:* Wednesday, April 25, 2012 8:58 AM
> *Posted To:* HYD General
> *Conversation:* Really heart touching.
> *Subject:* Really heart touching.
>
> ** **
>
> *Got as Forwarded…*
>
> * *
>
> Really heart touching.
> *
> I was walking around in a Big Bazaar store making shopping, when I saw
> a  Cashier talking to a boy couldn't have been more than 5 or 6 years
> old..
>
>
> The Cashier said, 'I'm sorry, but you don't have enough money to buy
> this doll. Then the little boy turned to me and asked: ''Uncle, are
> you sure I don't have enough money?''
>
> I counted his cash and replied: ''You know that you don't have enough
> money to buy the doll, my dear.'' The little boy was still holding the
> doll in his hand.
>
> Finally, I walked toward him and I asked him who he wished to give
> this doll to. 'It's the doll that my sister loved most and wanted so
> much . I wanted to Gift her for her BIRTHDAY.
>
>
> I have to give the doll to my mommy so that she can give it to my
> sister when she goes there.' His eyes were so sad while saying this.
> 'My Sister has gone to be with God.. Daddy says that Mommy is going to
> see God very soon too, so I thought that she could take the doll with
> her to give it to my sister...''
>
> My heart nearly stopped. The little boy looked up at me and said: 'I
> told daddy to tell mommy not to go yet. I need her to wait until I
> come back from the mall.' Then he showed me a very nice photo of him
> where he was laughing. He then told me 'I want mommy to take my
> picture with her so my sister won't forget me.' 'I love my mommy and I
> wish she doesn't have to leave me, but daddy says that she has to go
> to be with my little sister.' Then he looked again at the doll with
> sad eyes, very quietly..
>
> I quickly reached for my wallet and said to the boy. 'Suppose we check
> again, just in case you do have enough money for the doll?''
>
> 'OK' he said, 'I hope I do have enough.' I added some of my money to
> his without him seeing and we started to count it. There was enough
> for the doll and even some spare money.
>
> The little boy said: 'Thank you God for giving me enough money!'
>
>
> Then he looked at me and added, 'I asked last night before I went to
> sleep for God to make sure I had enough money to buy this doll, so
> that mommy could give It to my sister. He heard me!'' 'I also wanted
> to have enough money to buy a white rose for my mommy, but I didn't
> dare to ask God for too much. But He gave me enough to buy the doll
> and a white rose. My mommy loves white roses.'
>
>
> I finished my shopping in a totally different state from when I
> started. I couldn't get the little boy out of my mind. Then I
> remembered a local
>
> newspaper article two days ago, which mentioned a drunk man in a
> truck, who hit a car occupied by a young woman and a little girl. The
> little girl died right away, and the mother was left in a critical
> state. The family had to decide whether to pull the plug on the
> life-sustaining machine, because the young woman would not be able to
> recover from the coma. Was this the family of the little boy?
>
> Two days after this encounter with the little boy, I read in the news
> paper that the young woman had passed away.. I couldn't stop myself as
> I bought a bunch of white roses and I went to the funeral home where
> the body of the young woman was exposed for people to see and make
> last wishes before her burial. She was there, in her coffin, holding a
> beautiful white rose in her hand with the photo of the little boy and
> the doll placed over her chest. I left the place, teary-eyed, feeling
> that my life had been changed forever...
>
> The love that the little boy had for his mother and his sister is still,
> to this day, hard to imagine. And in a fra

Re: [GENERAL] idle in transaction process

[Abbas]

Best Regards,
Abbas


On Mon, Aug 15, 2011 at 11:14 PM, tamanna madaan <
tamanna.mad...@globallogic.com> wrote:

> Hi All
>
> I am using postgres-8.4.0 on a cluster setup with slony-2.0.4 being used
> for replication.
> Recently , I saw a "idle in transaction" postgres process as below.
>
> postgres 13052 14742 0 May13 ? 00:00:00 postgres: slon abc
> 172.16.1.1(49017) idle in transaction
> I wonder what could have lead to that hung postgres process . I googled
> about it a lot and they say that it could be
> because of abrupt netwotk issue between slony and postgres . But in my case
> slon was connected
> to its local postgres database. So, network wont be an issue in this case .
> What else could be the reason for
> this hung process ? What should I do to come over this kind of issue in
> future. I think this hung process would have
> taken locks on various tables. I wonder if killing the "idle in
> transaction" process would cause the locks on the tables
> to be released or not. Can anyone please  help me on that.
>

Of course it is a slon process if it is not due to a network issue, then
might be any of your scripts, if not you can try by restarting the slon
process on origin.

Abbas.

>
> Thanks in Advance .
>
> Tamanna
>
>
>
>
>
>

Re: [GENERAL] Trigger get dissabled

[Abbas]

On Mon, Jun 14, 2010 at 3:52 PM, Gaurav K Srivastav wrote:

> Hi,
>
> I am using  postgre SQL 8.3 on centos,
>
> My case is Suppose  I a databse ABC (In this database I have few enabled
> trigger) and I am making  abc.tar as a dump file using pg_dump utility.
> Now I am restoring abc.tar using pg_restore on another machine .
>
> Up to now every thing is fine but the trigger get dissabled.
>
> Can you please let me know that how can I again enable these triggers in my
> database schema?
>
> Or Is there any way to not make trigger dissable while pg_dump or
> pg_restore?
>
> Or is there any query to enable all the dissabled triggers in a database
> schema?
>
> Please let me know I will be highly obliged.
>
>
>
> Are you sure that you didn't use  --disable-triggers option while taking
the pg_dump?


---
Abbas.

> --
> Thanks & Regards
> Gaurav K Srivastav
>

Re: [GENERAL] pg_ctl with unix domain socket?

[Abbas]

On Tue, Sep 1, 2009 at 4:58 PM, Josef Wolf  wrote:

> Hello,
>
> I have created a fresh cluster with
>
>   initdb -D /some/path/pgtest
>
> I can start postgres to run on unix domain socket serving this cluster
> with:
>
>   postgres -D /some/path/pgtest -h '' -k /some/path/pgtest
>
> But I'd like to use pg_ctl instead, in order to have clean control:
>
>   PGPORT=/some/path/pgtest pg_ctl -D/some/path/pgtest -l postgreslog start
>
> PGPORT should be the port number on which the Postgresql server is
running(5432 is default), and  PGDATA=some/path/pgtest .

To start the postgresql server using pg_ctl you can use,

pg_ctl -D PGDATA start

to stop ,

pg_ctl -D PGDATA stop


Abbas.


> Any hints how to use pg_ctl to start/stop postgresql on a unix domain
> socket?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Drop Cluster]

[Abbas]

On Mon, Aug 3, 2009 at 11:11 AM,  wrote:

>
>
>
>
> >
> > It seems you are trying to drop a replication cluster, to drop a
> > this you have to do as follows:
> >
>
> Yes it is a replication cluster. I am using Slony to replicate
> my 8.1.11 database on CentOS 5.
>
>
> > connect to the node 1(i.e., Master DB) database, check whether the
> > _replcluster schema is already exists or not by giving the command  "/dn"
> >
>
>  this give -->
>
> [r...@quirinus pg_log]# su - postgres
> -bash-3.2$ /dn
> -bash: /dn: No such file or directory
> -bash-3.2$ dn
> -bash: dn: command not found
>
> Need to fire these commands by connecting to your database.


>
>
>  Regards,
>  Sweta
>
>
>
>

Re: [GENERAL] Password?

[Abbas]

On Wed, Jul 8, 2009 at 4:12 PM, Andreas Wenk  wrote:

> Ms swati chande schrieb:
>
>> --- On *Wed, 7/8/09, Andreas Wenk //*
>> wrote:
>>
>>
>>From: Andreas Wenk 
>>Subject: Re: [GENERAL] Password?
>>To: "Ms swati chande" , "PG-General Mailing List"
>>
>>Date: Wednesday, July 8, 2009, 3:47 PM
>>
>>Ms swati chande schrieb:
>> > Thanks Andy,
>> >  I am working on Windows XP. Have built from source using Visual
>>Studio 2005.
>> >  I have made a change in pg_hba.conf to include the ipconfig of
>>my system.
>> > # TYPE  DATABASEUSERCIDR-ADDRESS  METHOD
>> >  *hostall all   trust*
>> >  # IPv4 local connections:
>> > hostall all 127.0.0.1/32  trust
>> > # IPv6 local connections:
>> > #hostall all ::1/128   trust
>>
>
Yes, the * sign should removed and have to mention listen_addresses = ' * '
in Postgresql.conf file.


>
>> >
>> >  This was to take care of the following problem:
>> >  LOG: could not bind IPv4 socket: Address already in use
>> > HINT: Is another postmaster already running on port 5432? If not,
>>wait a few seconds and retry.
>> > WARNING: could not create listen socket for "*"
>> > FATAL: could not create any TCP/IP sockets
>> >  For this I changed the listen_addresses to my current ip. and
>>made the same change in pg_hba.conf.
>> >  Thanks
>> >  Regards
>> > Swati
>> >
>>So does it work now ? Why is there a * sign before host? This seems
>>to be incorrect ...
>>
>>P.S.: dont' forget to reply also to the mailinglist (reply to all)
>>
>>
>>  >
> > No its still not working.
> > The * doesn't exist in pg_hba. It was probably in the mail as I had
> > formatted that line to be 'bold'.
>
> ah ok ..
>
> Actually it should work if you set listen_addresses to '*' in
> postgresql.conf. Did you change anything else in postgresql.conf or
> pg_hba.conf?
>
> I am not too experienced with Windows so maybe someone with more knowledge
> is able to find the trick (I installed 8.4 once with the one click installer
> ...no problems at all). But as far as I understand something is wrong with:
>
> > WARNING: could not create listen socket for "*"
> > FATAL: could not create any TCP/IP sockets
>
> I understand correct, that you fixed this? Then it should work as I
> mentioned earlier ...
>
> Cheers Andy
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Password?

[Abbas]

On Wed, Jul 8, 2009 at 3:22 PM, Andreas Wenk  wrote:

> Andreas Wenk schrieb:
>
>> Ms swati chande schrieb:
>>
>>> Hi
>>>  When I write the following commands at the prompt,
>>>
>>>  >createuser -S -d -R user1
>>>  > createdb sample
>>>
>>> I am asked to enter a password. I have not set any password anywhere.
>>> Which password is it asking for?
>>> Please help.
>>> I have built from source on Windows XP.
>>>
>>> Thanks is advance,
>>>
>>> Regards
>>> Swati
>>>
>> Hi Swati,
>>
>> what are the setting of your pg_hba.conf? I assume that there is a entry
>> like this:
>>
>> # TYPE  DATABASEUSERCIDR-ADDRESS  METHOD
>>
>> # "local" is for Unix domain socket connections only
>> local   all all   password
>>
>>
>> That means that the password you are asked is the password of the standard
>> user for your cluster - commonly postgres.
>>
>> Cheers
>>
>> Andy
>>
>
> *argh* - more detailed to avoid confusion. The auth method 'password' in
> pg_hba.conf means, that you will be asked for a password for the user you
> try to create a db with. If no user is given (with createdb -U [username]),
> this user is postgres ...
>
> see also createdb --help for options ...
>
>
> Cheers
>
> Andy
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

  If you don't need the password authentication you have to edit the
pg_conf file and replace "password" with "trust", after this reload the
cluster. It won't prompt you for password.

Thanks,
Abbas.

Re: [GENERAL] Postgres Plus Advanced Server and general Postgres compatibility?

[Abbas]

On Sun, Jul 5, 2009 at 4:49 PM, Andre Lopes  wrote:

> Hi,
>
> I have installed "Postgres Plus Advanced Server" in a developement machine.
> TAt this time I don't know the production server envoirement. It is
> problematic to use "Postgres Plus Advanced Server" as developement server?
> Could I have some compatibility problems?
>
> Best Regards,
> André.
>

It won't be problematic to use as a dev server or as a prod server also. You
won't face any compatibility problems.

Abbas.

Re: [GENERAL] slow select in big table

[Abbas]

On Fri, Apr 3, 2009 at 2:18 AM, rafalak  wrote:

> Hello i have big table
> 80mln records, ~6GB data, 2columns (int, int)
>
> if query
> select count(col1) from tab where col2=1234;
> return low records (1-10) time is good 30-40ms
> but when records is >1000 time is >12s
>
>
> How to increse performace ?
>
>
> my postgresql.conf
> shared_buffers = 810MB
> temp_buffers = 128MB
> work_mem = 512MB
> maintenance_work_mem = 256MB
> max_stack_depth = 7MB
> effective_cache_size = 800MB
>
>
> db 8.3.7
> server, atlon dual-core 2,0Ghz, 2GB RAM, SATA
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Is the table has indexes?
Decreasing the work_mem also increase performance.
Monitor these changes by explain the query plan.

Regards,
Abbas.

Re: [GENERAL] Locking entire database

[Sibte Abbas]

On 9/14/07, Panagiotis Pediaditis <[EMAIL PROTECTED]> wrote:
> Hello,
>
> Is there some way of locking all database tables in a transaction
> without knowing their names
> or even better just locking the entire database? I know this is bad
> tactics but there is a specific
> case where i need it. Can it be done?
>

AFAIK Locking the entire database may not be an option in postgresql.
However you can virtually restrict access to everyone to a particular
database via pg_hba.conf.

More details here
http://www.postgresql.org/docs/8.2/static/auth-pg-hba-conf.html

regards,
--
Sibte Abbas

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

Re: [GENERAL] Getting result from EXECUTE

[Sibte Abbas]

On 9/8/07, Robert Fitzpatrick <[EMAIL PROTECTED]> wrote:
>
> I have a trigger function that I want to apply to several tables, hence
> my use of TG_RELNAME. I just want the record to get inserted if an
> UPDATE comes from my view rule if the record for the client doesn't
> already exist. This is what I have, but I'm finding the FOUND is not
> returned for EXECUTE. How can I accomplish what I need?
>
> CREATE OR REPLACE FUNCTION "public"."create_fldclientnumber_trigg_func" ()
> RETURNS trigger AS'
> begin
>   EXECUTE ''SELECT * FROM '' || TG_RELNAME || '' WHERE fldclientnumber =
> '' || NEW.fldclientnumber;
>   IF NOT FOUND THEN
>  EXECUTE ''INSERT INTO '' || TG_RELNAME || '' (fldclientnumber) VALUES
> ('' || NEW.fldclientnumber || '')'';
>   END IF;
>   RETURN NEW;
> end;
> 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> Thanks for the help.
>
> --
> Robert
>
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>

AFAIK the FOUND variable does not get updated as part of an EXECUTE command.


Consider using a strict INTO clause alongwith EXECUTE. This way a
NO_DATA_FOUND exception will be generated if your query did'nt return any
data. Something like this:

DECLARE

v_rec record;
BEGIN

EXECUTE ''SELECT * FROM '' || TG_RELNAME || '' WHERE fldclientnumber = '' ||
NEW.fldclientnumber INTO STRICT v_rec;


EXCEPTION
when no_data_found then
/* do something */

regards,
--
Sibte Abbas

Fwd: [GENERAL] Query the catalog

[Sibte Abbas]

-- Forwarded message --
From: Sibte Abbas <[EMAIL PROTECTED]>
Date: Aug 31, 2007 8:32 AM
Subject: Re: [GENERAL] Query the catalog
To: Ottó Havasvölgyi <[EMAIL PROTECTED]>

On 8/31/07, Ottó Havasvölgyi <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> How can I query the following information from the catalog:


You can find guc variables for both of these compile time constants:


- max number of index columns;


show  max_index_keys;

- max number of function arguments;


show max_function_args;


As far as I know these are compile-time constants, default is 32.
>
> Thanks,
> Otto



regards,
-- Sibte

Re: [GENERAL]

[Sibte Abbas]

On 8/25/07, Thobiyas <[EMAIL PROTECTED]> wrote:
> Dear sir
> how can get the  log file in postgres sql
>
> --
> **
> P.Maria Antony Thobiyas
> Bosco InfoTech Pvt Ltd
>
> Mobile: 09486144070 (Personal)
>  **

By default the log files are stored in the $PGDATA/pg_log folder.

regards,
-- Sibte

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

Re: [GENERAL] parsed queries (cursors) cashing issues

[Sibte Abbas]

On 8/3/07, Sergey Moroz <[EMAIL PROTECTED]> wrote:
> No that is not I meant. The problem in Prepared statements is in that you
> should determine SQL inside the function. I want to pass a query as a
> parameter, as well as query parameters.
> For example (I want to create a function like the following):
>
> select *
>   from exec_query(
>   /*query text  => */  'select f1, f2 from table
> where f3 = $1' ,
>/*param1  => */  1::integer
>   )
>  as (f1 integer, f2 text)
>
> so function exec_query got a query text as parameter, query parameters,
> executed it and returned result as SETOF. In case of such a query had been
> executed at least once, prepare step should be excluded (stored execution
> plan should be used).
>

In this case you need to store query text along with its plan name.
This will allow you to simply execute the plan each time a previously
parsed/planned query is executed.

However storing raw queries can be a *very* expensive operation, not
to mention the high cost of performing comparison on them. Due to the
associated cost, I'll
recommend using(and storing) hashes for query text.

If I were you, i'll write the hash calculation and storage and
retrieval functions in C and the top level function in Plpgsql.

Hope that helps.

regards,
-- Sibte

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

Re: [GENERAL] parsed queries (cursors) cashing issues

[Sibte Abbas]

On 8/2/07, Sergey Moroz <[EMAIL PROTECTED]> wrote:
> The problem is that I can't find the way to exclude query parsing (prepare
> step) for custom queries. In other words I want to create a function that
> accepts a query text with "$1, $2, etc." and variables as params, executes
> the query and returns a set of record. I could use 'execute' in plpgsql but
> in such case a query will be parsed each time it is called. I check SPI and
> found the way to store execution plans for the duration of the session, but
> no convenient way to check if the plan was already generated for the query.
> So I should create and store hash table by myself, and associate plan
> pointers and query hash by myself. I'm not a C/C++ guy so it's not an easy
> task for me :). Is there any way to solve the problem? By the way - why not
> to store hashes for queries and execution plans in a shared pool to have an
> opportunity not to parse already parsed queries for any session as Oracle
> does?
>

Looks like you want to cache the query plans and then simply execute
them in subsequent invocations. The answer to this is Prepared
statements. Go to
http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html for
more details.

regards,
-- Sibte

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

   http://archives.postgresql.org/

Re: [HACKERS] [GENERAL] 8.2.4 signal 11 with large transaction

[Sibte Abbas]

On 7/23/07, Tom Lane <[EMAIL PROTECTED]> wrote:


No, the place that has to change is where errstart() detects that we're
recursing.  We could possibly have it first try to make a shorter string
and only give up entirely if recursion happens again, but given that
this is such a corner case I don't think it's worth the complexity and
risk of further bugs.  I've made it just drop the statement at the same
time that it decides to give up on printing other context (which can
also be a source of out-of-memory problems btw).
http://archives.postgresql.org/pgsql-committers/2007-07/msg00215.php



Makes sense.

regards,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

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

Re: [GENERAL] Debugging postgresql source on gdb

[Sibte Abbas]

On 7/22/07, Sibte Abbas <[EMAIL PROTECTED]> wrote:

On 7/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> Shreya Bhargava <[EMAIL PROTECTED]> writes:
> > 1. gdb postgres
> > 2. set args -D test (test is my dbcluster)
> > 3. b hashbuild(this is the function i want to break on)
> > 4. run
>
> You've set the breakpoint in the postmaster process.  It won't propagate
> to child backends, at least not without special gdb pushups.
>
> The way that I usually debug things is to start the client psql job,
> then determine the PID of the backend serving it, and "attach" to
> that process in gdb.
>
> In a development environment where you're likely to have only one or
> a few backends running, this shell script might help:
>
> #!/bin/sh
>
> # tee /dev/tty is for user to see the set of procs considered
> PROCS=`ps auxww | \
> grep postgres: | \
> grep -v -e 'grep postgres:' -e 'postgres: stats' -e 'postgres: writer' -e 
'postgres: archiver' -e 'postgres: logger' -e 'postgres: autovacuum' | \
> tee /dev/tty | \
> awk '{print $2}'`
>
> if [ `echo "$PROCS" | wc -w` -eq 1 ]
> then
> exec gdb $PGINSTROOT/bin/postgres -silent "$PROCS"
> else
> exec gdb $PGINSTROOT/bin/postgres -silent
> fi
>
> This will attach directly to the target backend if there's only one,
> else you can examine the ps output to determine which PID to attach to.
>
> regards, tom lane
>

Also, for gdb to function properly, you should compile the source with
--enable-debug and no compiler optimization i.e:

./configure --enable-debug && CFLAGS=-O0



"&&" was a typo, sorry for that. The actual command is:

./configure --enable-debug CFLAGS=-O0

regards,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

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

Re: [GENERAL] Debugging postgresql source on gdb

[Sibte Abbas]

On 7/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:

Shreya Bhargava <[EMAIL PROTECTED]> writes:
> 1. gdb postgres
> 2. set args -D test (test is my dbcluster)
> 3. b hashbuild(this is the function i want to break on)
> 4. run

You've set the breakpoint in the postmaster process.  It won't propagate
to child backends, at least not without special gdb pushups.

The way that I usually debug things is to start the client psql job,
then determine the PID of the backend serving it, and "attach" to
that process in gdb.

In a development environment where you're likely to have only one or
a few backends running, this shell script might help:

#!/bin/sh

# tee /dev/tty is for user to see the set of procs considered
PROCS=`ps auxww | \
grep postgres: | \
grep -v -e 'grep postgres:' -e 'postgres: stats' -e 'postgres: writer' -e 
'postgres: archiver' -e 'postgres: logger' -e 'postgres: autovacuum' | \
tee /dev/tty | \
awk '{print $2}'`

if [ `echo "$PROCS" | wc -w` -eq 1 ]
then
exec gdb $PGINSTROOT/bin/postgres -silent "$PROCS"
else
exec gdb $PGINSTROOT/bin/postgres -silent
fi

This will attach directly to the target backend if there's only one,
else you can examine the ps output to determine which PID to attach to.

regards, tom lane



Also, for gdb to function properly, you should compile the source with
--enable-debug and no compiler optimization i.e:

./configure --enable-debug && CFLAGS=-O0

regards,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

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

Re: [GENERAL] 8.2.4 signal 11 with large transaction

[Sibte Abbas]

On 7/20/07, Tom Lane <[EMAIL PROTECTED]> wrote:


I guess what we need to do is hack the emergency-recovery path for
error-during-error-processing such that it will prevent trying to print
a very long debug_query_string.  Maybe we should just not try to print
the command at all in this case, or maybe there's some intermediate
possibility like only printing the first 1K or so.  Thoughts?

regards, tom lane



I think printing the first 1K would make more sense.

If I understand you correctly, the code path which you are referring
to is the send_message_to_server_log() function in elog.c?

thanks,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

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

Re: [GENERAL] DB crashed and duplicated template0 db

[Sibte Abbas]

On 7/6/07, Luki Rustianto <[EMAIL PROTECTED]> wrote:

Hi,

One of my customer's server goes down, I don't know yet how, but when
when they restart the machine I got this:
1. DB is working like usual
2. template0 and template1 database is *duplicated*
3. I can't do pg_dump, because error returned (see below)

How can I backup the database (other than template0 and template1) ?
How can this happen (duplicated db entry) ?

below is the log:

[EMAIL PROTECTED] ~]$ psql -Utest -l
Password:
  List of databases
 Name |  Owner   | Encoding
--+--+---
 template0| postgres | SQL_ASCII
 template0| postgres | SQL_ASCII
 template1| postgres | SQL_ASCII
 template1| postgres | SQL_ASCII
 tmed  | test | SQL_ASCII
 tmed_drei | test | SQL_ASCII
(6 rows)

[EMAIL PROTECTED] ~]$ pg_dump -x -O -Utest tmed > db.sql   
Password:
pg_dump: query to obtain list of schemas failed: ERROR:  more than one
row returned by a subquery used as an expression

[EMAIL PROTECTED] ~]$ psql -Utest teramed -c "SELECT COUNT(*) FROM
regpatient"Password:
 count

 135223
(1 row)

[EMAIL PROTECTED] ~]$ psql  --version
psql (PostgreSQL) 7.4.8
contains support for command-line editing


thx.



This can happen when your index is/are corrupted. Try issuing a
reindex command.

regards,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

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

Re: [GENERAL] Dynamic Log tigger (plpgsql)

[Sibte Abbas]

On 6/16/07, Noah Heusser <[EMAIL PROTECTED]> wrote:

Hi

I want to implement a trigger-function witch can fill the following table.
Each data manipulation (INSERT, UPDATE or DELETE) gets logged.
The function should work as trigger on diffrent tables.

CREATE TABLE logtable (
  operationCHAR(6) CHECK (change_type IN ('DELETE', 'INSERT', 
'UPDATE')),
  tablenameVARCHAR,
  rowidINTEGER,  -
  touched_columns  VARCHAR[]
);

My Problem is in the last Column (touched_columns).
If it was an UPDATE Operation, I just need to know witch columns changed. (I am 
not iterrestet in the old or new value)
=> IF OLD.columnName != NEW.columnName, it has changed.



My Question:
How can I do "OLD.columnName != NEW.columnName" if I don't know what the
columnNames are at Compile Time?
I have the columnName in a variable.


Thx for help.
Noah



Are you trying to do this from a plpgsql function? If so then I think
you should try to do this from a C function.

With C functions you will get more control over the new and old
versions of the tuple since you get their pointers via
TriggerData->tg_trigtuple (old tuple) and TriggerData->tg_newtuple
(new tuple).

--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

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

[GENERAL] help me...

[HIRA SIROJUDIN ABBAS]

Hi, Mr. PG..
what is PG version operating OLAP operations ?
Thanks..

--Hira Sirojudin-- 
--Jurusan Teknik Komputer dan Informatika - Politeknik Negeri Bandung




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

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

Re: [GENERAL] Checking cursor's state in plpgsql

[Sibtay Abbas]

thankz :-)

On 4/10/05, Bruce Momjian  wrote:
> Sibtay Abbas wrote:
> > Hello all
> >
> > Can we check whether a cursor is OPEN or not in plpgsql.
> > Like in oracle you can check a CURSOR'S state with %ISOPEN
> >
> No, but we do have a TODO item:
> 
> o Allow pooled connections to list all open WITH HOLD cursors
> 
> --
>   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 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] Checking cursor's state in plpgsql

[Sibtay Abbas]

Hello all

Can we check whether a cursor is OPEN or not in plpgsql.
Like in oracle you can check a CURSOR'S state with %ISOPEN

After navigating the documentation i was not able to find an
equivalent in plpgsql.

Do we have an alternative to this in plpgsql?

Thankz

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

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

[GENERAL] parameters handling in postgresql functions

[Sibtay Abbas]

hi all
How are function parameters generally passed in postgresql.

More specifically when we pass a variable to a plpgsql function is
passed by value or by reference

Thank You

---(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] Problem with NOT IN and Sub-Select

[Sibtay Abbas]

Add "NOT NULL" keyword in the WHERE clause of your SUB SELECT statement

something like that

..NOT IN ( SELECT contact_id FROM
tb_contact_role WHERE  type2 <> 72 AND type2  NOT NULL)



On Wed, 26 Jan 2005 15:03:17 +, mike <[EMAIL PROTECTED]> wrote:
> I have the following query
> 
> SELECT vw_mail_add.contact_id, vw_mail_add.first_name,
> vw_mail_add.last_name, vw_mail_add.address1, vw_mail_add.add2,
> vw_mail_add.add3, vw_mail_add.add4, vw_mail_add.add5, vw_mail_add.add6
> FROM vw_mail_add JOIN tb_contact_role ON vw_mail_add.contact_id =
> tb_contact_role.contact_id WHERE tb_contact_role.type2 = 72 AND
> tb_contact_role.contact_id NOT IN ( SELECT contact_id FROM
> tb_contact_role WHERE  type2 <> 72);
> 
> which gives
> 
>  contact_id | first_name | last_name | address1 | add2 | add3 | add4 |
> add5 | add6
> ++---+--+--+--+--
> +--+--
> (0 rows)
> 
> (aim is to select all records which do not appear in a different
> category)
> 
> However if I two tables from the queries and do a right join I get
> SELECT DISTINCT
> tb_client_list1.contact_id,tb_client_list2.contact_id,tb_client_list1.type2 
> FROM tb_client_list1 RIGHT JOIN tb_client_list2 ON 
> tb_client_list1.contact_id=tb_client_list2.contact_id WHERE 
> tb_client_list1.contact_id IS NULL ORDER BY tb_client_list2.contact_id;
> 
> 196 rows
> 
> Anyone any idea what is going wrong here?
> 
> ---(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
>

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

Re: [GENERAL] working with multidimensional arrays in plpgsql

[Sibtay Abbas]

On Wed, 26 Jan 2005 12:40:24 +0500, Sibtay Abbas <[EMAIL PROTECTED]> wrote:
> hello everyone
> 
> i am having problem with multidimensional arrays in plpgsql following
> is the source code of the function which i am trying to run
> 
> CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$
> DECLARE
> x INTEGER[10][10];
> tmp VARCHAR(40);
> BEGIN
> x[3][1] := '20';   --i have even tried x[3][1] = 20
> tmp := x[3][1];
> RAISE NOTICE '%', tmp;
> RETURN;
> END;
> $$LANGUAGE 'plpgsql';
> 
> As you might have observed here, the actual problem is
> how to do assignment to multidimensional array locations using the
> subscript operater.
> 
> Thank you
> 

Something which i forgot to mention is that the output of the above
mentioned procedure is
NOTICE: NULL

which means and x[3][1] was not initiliazed with the statement
x[3][1] := '20';

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

[GENERAL] working with multidimensional arrays in plpgsql

[Sibtay Abbas]

hello everyone

i am having problem with multidimensional arrays in plpgsql following
is the source code of the function which i am trying to run

CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$
DECLARE
x INTEGER[10][10];
tmp VARCHAR(40);
BEGIN
x[3][1] := '20';   --i have even tried x[3][1] = 20
tmp := x[3][1];
RAISE NOTICE '%', tmp;
RETURN;
END;
$$LANGUAGE 'plpgsql';

As you might have observed here, the actual problem is
how to do assignment to multidimensional array locations using the
subscript operater.

Thank you

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

   http://archives.postgresql.org