ray madigans.org | 3 Sep 01:43 2015

Retrieve postgres password

I have lost/forgotten the postgres password for my installation so I did the following.

I edited pg_hba.conf and added an entry:

local all all trust

restarted postgresql.

loged in as postgres (psql template1 -U postgres)

change the postgres password with

ALTER ROLE postgres PASSWORD foo

I edited pg_hba.conf and get rid of the added line

restarted postgres

when I attempted to reenter with 

psql template1 -U postgres --password

I get an error Peer authentication failed for user "postgres"


Not sure what I did wrong, any help would be appreciated. 

Tom Smith | 3 Sep 01:14 2015
Picon

get first and last row in one sql as two columns

Hi:

I need to get the first and last tow in one sql like below

select first(col1), last(col1) from table order by col1

I saw some posting in wiki with a custom function (or C extention)
to do this.   Is it widely used and reliable?
https://wiki.postgresql.org/wiki/First/last_(aggregate)

I am wondering why these two functions are not part of postgresql built-in
functions as it has many use cases

Thanks
Ray Stell | 2 Sep 23:00 2015
Picon

bdr admin role

This doc specifies to initdb with the admin user "postgres,"
http://bdr-project.org/docs/stable/quickstart-instances.html
but if I do that the supervisor falls over with:
$ cat bdr5598.log
LOG:  registering background worker "bdr supervisor"
LOG:  database system was shut down at 2015-09-02 16:04:45 EDT
LOG:  starting up replication identifier with ckpt at 0/171EBF8
LOG:  MultiXact member wraparound protections are now enabled
LOG:  starting background worker process "bdr supervisor"
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
LOG:  Created database bdr_supervisordb (oid=16384) during BDR startup
LOG:  worker process: bdr supervisor (PID 21666) exited with exit code 1
FATAL:  role "postgresql" does not exist
LOG:  starting background worker process "bdr supervisor"

It works if I init with "-U postgresql"

--

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

Rémi Cura | 2 Sep 13:44 2015
Picon

(expert) "insert into VIEW returning" inside an instead of trigger returns nothing

Hey list,
I'm stuck on a problem that I can't figure out (postgres 9.3).
In short, using an
INSERT INTO __view_with_trigger__ ...  RETURNING gid INTO _gid;
returns nothing.
I need this feature because I use views on tables as user interface.

​This must have to do with the postgres order of execution,
because inserting into a table instead of the view of the table returns the expected result.

Here is a synthetic example (of course the real use really requires this kind of architecture),
any help is much appreciated,
because I don't see any work-around (except not using view at all, which would be terrible data duplication in my case)

Cheers,
Rémi-C​



------------------------------------------------
-- test inserting and instead of trigger --
-----------------------------------------------

CREATE SCHEMA IF NOT EXISTS test ;
SET search_path to test, public ;

DROP TABLE IF EXISTS generic_object CASCADE;
CREATE TABLE generic_object (
    gid SERIAL PRIMARY KEY
    , orientation float
) ;

DROP VIEW IF EXISTS editing_generic_object ;
CREATE VIEW editing_generic_object AS(
    SELECT gid,
        degrees(orientation) AS orientation
        FROM generic_object
) ;


DROP TABLE IF EXISTS specific_object CASCADE ;
CREATE TABLE specific_object (
    gid int references generic_object (gid) ON DELETE CASCADE
    , width float
) ;

DROP VIEW IF EXISTS editing_specific_object ;
CREATE VIEW editing_specific_object AS(
    SELECT g.gid    
        , g.orientation
        , so.width
        FROM specific_object AS so LEFT OUTER JOIN
            generic_object AS g USING (gid)
) ;




DROP FUNCTION IF EXISTS test.rc_editing_generic_object() CASCADE ;
CREATE OR REPLACE FUNCTION test.rc_editing_generic_object(  )
RETURNS  trigger  AS $BODY$
/** <at> brief : this trigger deals with editing generic object*/
    DECLARE      
    BEGIN
        IF TG_OP = 'DELETE' THEN DELETE FROM test.generic_object WHERE gid = OLD.gid ; RETURN OLD ;
        ELSIF TG_OP = 'INSERT' THEN INSERT INTO test.generic_object (orientation) VALUES (radians(NEW.orientation) ) ;
        ELSE UPDATE test.generic_object SET orientation = radians(NEW.orientation) ;
        END IF ;
       
        RETURN NEW ;
    END ;
$BODY$  LANGUAGE plpgsql VOLATILE;
 

DROP TRIGGER IF EXISTS rc_editing_generic_object ON test.editing_generic_object ;
CREATE TRIGGER rc_edit_street_object_pedestrian INSTEAD OF UPDATE OR INSERT OR DELETE
ON test.editing_generic_object
FOR ROW  EXECUTE PROCEDURE rc_editing_generic_object(  ) ;




DROP FUNCTION IF EXISTS test.rc_editing_specific_object() CASCADE ;
CREATE OR REPLACE FUNCTION test.rc_editing_specific_object(  )
RETURNS  trigger  AS $BODY$
/** <at> brief : this trigger deals with editing specific object*/
    DECLARE
        _gid int;
    BEGIN
        IF TG_OP = 'DELETE' THEN DELETE FROM test.generic_object WHERE gid = OLD.gid ; RETURN OLD ;
        ELSIF TG_OP = 'INSERT' THEN
            --does not works
            INSERT INTO test.editing_generic_object (orientation) VALUES ( NEW.orientation)  RETURNING gid INTO _gid;
            --does works
            --INSERT INTO test.generic_object (orientation) VALUES ( radians(NEW.orientation) )  RETURNING gid INTO _gid;
           
            RAISE WARNING 'here is the gid deduced after insertion : %', _gid ;
            INSERT INTO test.specific_object (gid, width) VALUES (_gid, NEW.width) ;
        ELSE
            UPDATE test.editing_generic_object  AS e SET orientation = NEW.orientation WHERE e.gid = NEW.gid;
            UPDATE test.specific_object AS s SET width = NEW.width WHERE s.gid = NEW.gid;
        END IF ;
        RETURN NEW ;
    END ;
$BODY$  LANGUAGE plpgsql VOLATILE;
 

DROP TRIGGER IF EXISTS rc_editing_specific_object ON test.editing_specific_object ;
CREATE TRIGGER rc_editing_specific_object INSTEAD OF UPDATE OR INSERT OR DELETE
ON test.editing_specific_object
FOR ROW  EXECUTE PROCEDURE rc_editing_specific_object(  ) ;

--testing

    --inserting into generic : works
    INSERT INTO editing_generic_object ( orientation) VALUES (180) ;
    SELECT *
    FROM generic_object ;
  
    -- insert into specific : don't work
    INSERT INTO editing_specific_object ( orientation,width) VALUES (180, 123) ;
    SELECT *
    FROM specific_object ; 
Willy-Bas Loos | 2 Sep 12:46 2015
Picon

FDW and BDR

Hi,

I've read that CREATE FOREIGN DATA WRAPPER currently is prohibited on BDR enabled databases. And other FDW and FTS related commands too.

This seems obvious, but i want to make sure:
Does that mean that FDW's are not supported at all in databases that use Bi-Directional Replication?
(maybe one could create the FDW before configuring replication)

Cheers,

--
Willy-Bas Loos
FarjadFarid(ChkNet | 2 Sep 11:05 2015

Serial initial and incremental value

Sorry folks,

 

I should have included this in my last question.

 

How can I obtain the initial and incremental value of a serial entity?

 

Many thanks

 

Best Regards

 

Farjad Farid

FarjadFarid(ChkNet | 2 Sep 10:42 2015

Re: Detecting autoincrement columns

Many thanks.

 

From: Melvin Davidson [mailto:melvin6925 <at> gmail.com]
Sent: 02 September 2015 00:20
To: Tom Lane
Cc: FarjadFarid(ChkNet); pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] Detecting autoincrement columns

 

Try this:

SELECT *
    FROM information_schema.columns
WHERE UPPER(column_default) LIKE 'NEXTVAL%'

ORDER BY 1, 2, 3;

 

On Tue, Sep 1, 2015 at 6:33 PM, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:

"FarjadFarid\(ChkNet\)" <farjad.farid <at> checknetworks.com> writes:
> Can someone please direct me as how to detect columns (serial) with
> autoincrement option ?

They have a default that depends on a nextval() call.

                        regards, tom lane



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




--

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

FarjadFarid(ChkNet | 2 Sep 00:13 2015

Re: Detecting autoincrement columns

Hi all, 

Can someone please direct me as how to detect columns (serial) with
autoincrement option ? 

Many thanks. 

Best Regards

Farjad Farid

--

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

Saravanakumar Murugesan | 31 Aug 12:46 2015

Postgresql path for json array

I have a json for country

Table column: countryJson

Sample Json :  [   { name:india,
       population: 1000M,
       type: country,
       children: [  
                    {
                    name: karnataga,
                    population: 30M,
                    type:state,
                    children: [   {
                                            name: bangalore,
                                            population: 10M,
                                            type:district   },
                            {
                                            name: mysore,
                                            population: 2M,
                                            type:district  

                        },
                   ]
}
]
}]


I need to index country name, state name and district name.  I need to find districts with population greater than 5M. How to query districts with statename = karnataga and country name = india?

i.e select countryJson[ name =''india"].children[name="karnataga"].children [population] > 5m?

Is it supported in postgresql? i.e better examples on array value indexes and array value criteria would help me to use easily use json.

cheers
Saravanakumar

 









This email and attachments may contain privileged or confidential information intended only for the addressee(s) indicated. The sender does not waive any of its rights, privileges or protections respecting this information. If you are not the named addressee, an employee, or agent responsible for sending this message to the named addressee (or this message was received by mistake), you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If received in error, please notify us immediately by e-mail, discard any paper copies and delete all electronic files of the email.

Computer viruses can be transmitted via email. The recipient should check this email and any attachments for viruses. Email transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender accepts no liability for any damage caused by any transmitted viruses or errors or omissions in the contents of this message.

Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560 www.overturenetworks.com
Ray Stell | 31 Aug 16:24 2015
Picon

bdr download

Two comments on the BDR docs:

The second option provided here, http://bdr-project.org/docs/stable/installation-source.html#INSTALLATION-SOURCE-PREREQS
"3.3.2.2 Downloading release source tarballs," seems to be an endless loop between http://bdr-project.org/ and http://2ndquadrant.com/en/resources/bdr/

Might want to edit here: http://bdr-project.org/docs/stable/appendix-signatures.html
" The BDR / 9.4 RPM releases key key is in turn signed..."

Thanks,
Ray
Saravanakumar Murugesan | 31 Aug 12:55 2015

FW: JsonArray value criteria



I have a json for country

Table column: countryJson

Sample Json :  [   { name:india,
       population: 1000M,
       type: country,
       children: [  
                    {
                    name: karnataga,
                    population: 30M,
                    type:state,
                    children: [   {
                                            name: bangalore,
                                            population: 10M,
                                            type:district   },
                            {
                                            name: mysore,
                                            population: 2M,
                                            type:district  

                        },
                   ]
}
]
}]


I need to index country name, state name and district name.  I need to find districts with population greater than 5M. How to query districts with statename = karnataga and country name = india?

i.e select countryJson[ name =''india"].children[name="karnataga"].children [population] > 5m?

Is it supported in postgresql? i.e better examples on array value indexes and array value criteria would help me to use easily use json.

cheers
Saravanakumar

 




This email and attachments may contain privileged or confidential information intended only for the addressee(s) indicated. The sender does not waive any of its rights, privileges or protections respecting this information. If you are not the named addressee, an employee, or agent responsible for sending this message to the named addressee (or this message was received by mistake), you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If received in error, please notify us immediately by e-mail, discard any paper copies and delete all electronic files of the email.

Computer viruses can be transmitted via email. The recipient should check this email and any attachments for viruses. Email transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender accepts no liability for any damage caused by any transmitted viruses or errors or omissions in the contents of this message.

Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560 www.overturenetworks.com

Gmane