Re: [GENERAL] Detecting autoincrement columns

[FarjadFarid(ChkNet)]

Many thanks. 

 

From: Melvin Davidson [mailto:melvin6...@gmail.com] 
Sent: 02 September 2015 00:20
To: Tom Lane
Cc: FarjadFarid(ChkNet); pgsql-general@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  wrote:

"FarjadFarid\(ChkNet\)"  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@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.   
 

[GENERAL] Serial initial and incremental value

[FarjadFarid(ChkNet)]

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

Re: [GENERAL] BDR setup - bdr_node_join_wait_for_ready()

[Craig Ringer]

On 2 September 2015 at 00:12, cchee-ob  wrote:
> Craig,
>
> By an existing  database, I mean I have a database that has our data on it.
> I have created the btree_gist and bdr extensions and ran the
> bdr.bdr_group_create () function and the bdr.bdr_node_join_wait_for_ready(),
> all run successfully.  After setting up the next BDR node, and after running
> bdr.bdr_group_join() function I run the bdr.bdr_node_join_wait_for_ready()
> function it just sits there and nothing returns.  It doesn't look like it's
> replicating data either.

As I said above, please check the PostgreSQL logs on both nodes. That
should generally be more informative. Also please show the output of
select * from bdr.bdr_nodes from both nodes.

The database for the 2nd node you were joining should've been
completely blank before you tried to join it. Was it? Try CREATE
DATABASE ... TEMPLATE template0; to make sure.

> What is the best practice for creating a BDR environment with 2 BDR nodes
> and 1 UDR node?

At this time UDR and BDR don't mix especially well. It's something
we're working to address, but is lower priority than getting more of
the underlying functionality into PostgreSQL 9.6 and streamlining the
user experience.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Serial initial and incremental value

[Thomas Kellerer]

FarjadFarid(ChkNet) schrieb am 02.09.2015 um 11:05:
> How can I obtain the initial and incremental value of a serial entity?


You can use:


  select * 
  from sequence_name;


where "sequence_name" is the name of the sequence associated with your column.


Thomas



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

[GENERAL] FDW and BDR

[Willy-Bas Loos]

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

Re: [GENERAL] FDW and BDR

[Willy-Bas Loos]

Sorry, forgot [GENERAL] in the subject at first.

On Wed, Sep 2, 2015 at 12:46 PM, Willy-Bas Loos  wrote:

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



-- 
Willy-Bas Loos

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

[Rémi Cura]

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$
/** @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$
/** @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 ;

[GENERAL] Re: (expert) "insert into VIEW returning" inside an instead of trigger returns nothing

[Rémi Cura]

I think I got it,
I have to always return something (like NEW) in the instead of trigger,
but fill NEW
with returnings of INSERT into regular table.
CHeers,
Rémi-C

2015-09-02 13:44 GMT+02:00 Rémi Cura :

> 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$
> /** @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$
> /** @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 ;
>

Re: [GENERAL] FDW and BDR

[Craig Ringer]

On 2 September 2015 at 18:46, Willy-Bas Loos  wrote:
> Sorry, forgot [GENERAL] in the subject at first.

You don't need to add it. The list manager software does that.

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

Correct.

The reason for this is that BDR replicates at a database level, but
CREATE SERVER and CREATE USER MAPPING are global, affecting all
databases on a PostgreSQL install. BDR can't therefore guarantee to
replicate CREATE SERVER to other nodes, since it might get run on a
non-BDR-enabled database.

If you CREATE FOREIGN TABLE it'll succeed (since the foreign server
exists locally) but might then fail on remote nodes. We've already
committed it locally, though. This will cause a stop-the-world
replication halt until the administrator intervenes by creating the
foreign server on the other nodes. It's made even worse by the impact
of the global DDL lock that's held until the CREATE FOREIGN TABLE
commits on the other nodes.

To prevent that, we reject CREATE FOREIGN TABLE.

Supporting this requires one of:

- Global DDL replication support in BDR. This is quite hard to do and
is not currently supported.

- 2-phase transaction DDL replication in BDR. Logical decoding would
have to support decoding prepared transactions for this, and it
doesn't currently. So it's 9.6 at the very soonest, and we have other
higher priorities for this.

- Detecting global dependencies in a statement and checking that all
peer nodes have those dependencies, then locking them before allowing
the DDL to commit locally. This requires nontrivial statement-specific
support and a bunch of extensions to how BDR nodes communicate.

We have higher priorities, like getting more of the BDR foundations
into 9.6 and meeting the needs of customers actively using BDR. So at
this point there's no set time frame on support for FDWs.

Basically, PostgreSQL having global objects when we replicate on a
per-database basis is messy. It creates issues with users/roles, 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?

Correct at this time. You can have FDWs in a different database on the
same Pg install, but not use FDWs in a BDR-enabled database.

> (maybe one could create the FDW before configuring replication)

No.

It's possible to override the filter using documented settings, but I
don't advise doing so unless you're extremely sure you need this, and
understand exactly what you're getting into. If you break it, you get
to keep the pieces.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] FDW and BDR

[Tom Lane]

Craig Ringer  writes:
> On 2 September 2015 at 18:46, Willy-Bas Loos  wrote:
>> I've read that CREATE FOREIGN DATA WRAPPER currently is prohibited on BDR
>> enabled databases. And other FDW and FTS related commands too.

> Correct.

> The reason for this is that BDR replicates at a database level, but
> CREATE SERVER and CREATE USER MAPPING are global, affecting all
> databases on a PostgreSQL install. BDR can't therefore guarantee to
> replicate CREATE SERVER to other nodes, since it might get run on a
> non-BDR-enabled database.

Uh ... what?

I do not know what the actual reason for this restriction is, but that
explanation is nonsense.  There are no shared catalogs involved with
FDWs.

regards, tom lane


-- 
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] Re: (expert) "insert into VIEW returning" inside an instead of trigger returns nothing

[Willy-Bas Loos]

On Wed, Sep 2, 2015 at 2:16 PM, Rémi Cura  wrote:

> I think I got it,
> I have to always return something (like NEW) in the instead of trigger,
> but fill NEW
> with returnings of INSERT into regular table.
>
>
Yes, the GID is detemined for the table.
But in test.rc_editing_generic_object(  ) you are reurning the NEW that you
just inserted from test.rc_editing_specific_object(  ).
The gid is filled for the table, but since you are doing an INSERT on the
table INSTEAD of the one that came in, the gid is not filled. The view does
not read from the table in this case, but writes, so the gid is not yet
available in NEW.

Cheers,

Willy-Bas

Re: [GENERAL] FDW and BDR

[Andres Freund]

On 2015-09-02 20:27:40 +0800, Craig Ringer wrote:
> The reason for this is that BDR replicates at a database level, but
> CREATE SERVER and CREATE USER MAPPING are global, affecting all
> databases on a PostgreSQL install. BDR can't therefore guarantee to
> replicate CREATE SERVER to other nodes, since it might get run on a
> non-BDR-enabled database.

As Tom explained thats not the case for either of those two. To my
knowledge the only reason those two commands aren't implemented is that
either nobody implemented the required ddl deparsing or, actually
somewhat likely, nobody removed the error check. Either way it should be
simple to implement.

Greetings,

Andres Freund


-- 
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] FDW and BDR

[Willy-Bas Loos]

Haha, that is funny :) It's always nice to see problems evaporate.
Thank's a lot for your answers.

On Wed, Sep 2, 2015 at 2:40 PM, Andres Freund  wrote:

> Either way it should be
> simple to implement.
>
>

-- 
Willy-Bas Loos

Re: [GENERAL] FDW and BDR

[Alvaro Herrera]

Andres Freund wrote:
> On 2015-09-02 20:27:40 +0800, Craig Ringer wrote:
> > The reason for this is that BDR replicates at a database level, but
> > CREATE SERVER and CREATE USER MAPPING are global, affecting all
> > databases on a PostgreSQL install. BDR can't therefore guarantee to
> > replicate CREATE SERVER to other nodes, since it might get run on a
> > non-BDR-enabled database.
> 
> As Tom explained thats not the case for either of those two. To my
> knowledge the only reason those two commands aren't implemented is that
> either nobody implemented the required ddl deparsing or, actually
> somewhat likely, nobody removed the error check. Either way it should be
> simple to implement.

Deparsing for FDW objects is in the patch that got committed for 9.5
(actually, Andres himself implemented those bits), but I'm not sure that
it was in the version used by the 9.4-BDR branch.  Anyway, even if it's
not there, it should be reasonably simple to lift the code from 9.5 into
BDR's branch.  And then remove the restrictions.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[GENERAL] bdr admin role

[Ray Stell]

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does PLPythonU support COPY table FROM string?

[Dominik Czarnota]

Unless I miss something, cursor won't help here - it is to retrieve data,
not to insert it.

I just need a missing feature of `plpy` to do COPY (actually doable, but
from file).

Anyway, after normalizing my tables and removing constraints and indexes
for the time of data loading, the current solution (COPY FROM file that is
saved a moment before...) is enough.
Although, if there is any place I can post to make a "feature request" to
add methods that would do postgres COPY in plpy, I would be thankful for
pointing me that place.

2015-08-29 1:34 GMT+02:00 Adrian Klaver :

> On 08/28/2015 10:12 AM, Dominik Czarnota wrote:
>
>> Oh... I have copied stacktrace generated from Python script which
>> connects to the db using psycopg2 driver, so that's where this
>> misleading psycopg2 error came from...
>>
>> About the list origin - I have to calculate a lot of stuff for each
>> stock on stock exchange market. Each calculation requires quotes from
>> the database - so to reduce io and not to fetch them everytime from
>> Python, I've created a plpythonu function that calculates everything for
>> each stock.
>> As the results also have to stay in the database, I need to use the
>> fastest possible method to insert a lot of data.
>>
>
> This means moving the results to another table?
>
> Have you looked at plpy.cursor here:
>
> http://www.postgresql.org/docs/9.4/interactive/plpython-database.html
>
>
>> Another problem for those calculations would be parallelizing them
>> somehow (I hope postgres clustering will do the work), but that's
>> totally unrelated to the problem we are discussing now.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] bdr admin role

[Melvin Davidson]

Initdb as user "postgred", not "postgresql"
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr admin role

[Melvin Davidson]

Oops postgres
On Sep 2, 2015 6:06 PM, "Melvin Davidson"  wrote:

> Initdb as user "postgred", not "postgresql"
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] get first and last row in one sql as two columns

[Tom Smith]

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

Re: [GENERAL] get first and last row in one sql as two columns

[Rob Sargent]

On 09/02/2015 05:14 PM, Tom Smith wrote:

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

If you're ordering by col1, does

   select min(col1), max(col1) from table order by col1

not do the trick;

Re: [GENERAL] get first and last row in one sql as two columns

[Rob Sargent]

On 09/02/2015 05:14 PM, Tom Smith wrote:

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
But what I think you are looking for are the windowing functions as what 
you propose breaks down pretty quickly with more complicated queries.

[GENERAL] Retrieve postgres password

[ray madigans.org]

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 trustrestarted postgresql. loged in as postgres (psql template1 -U postgres) change the postgres password withALTER ROLE postgres PASSWORD fooI edited pg_hba.conf and get rid of the added linerestarted postgreswhen I attempted to reenter with  psql template1 -U postgres --passwordI get an error Peer authentication failed for user "postgres"Not sure what I did wrong, any help would be appreciated. 

Re: [GENERAL] get first and last row in one sql as two columns

[Melvin Davidson]

Try this:

SELECT
  (SELECT 
 FROM 
 ORDER BY  offset 0 LIMIT 1) ,
  (SELECT 
 FROM 
 ORDER BY  OFFSET (SELECT COUNT(*) ) LIMIT 1)
   FROM  LIMIT 1;


On Wed, Sep 2, 2015 at 7:27 PM, Rob Sargent  wrote:

> On 09/02/2015 05:14 PM, Tom Smith wrote:
>
> 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
>
> If you're ordering by col1, does
>
> select min(col1), max(col1) from table order by col1
>
> not do the trick;
>



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

Re: [GENERAL] FDW and BDR

[Craig Ringer]

On 2 September 2015 at 20:40, Andres Freund  wrote:
> On 2015-09-02 20:27:40 +0800, Craig Ringer wrote:
>> The reason for this is that BDR replicates at a database level, but
>> CREATE SERVER and CREATE USER MAPPING are global, affecting all
>> databases on a PostgreSQL install. BDR can't therefore guarantee to
>> replicate CREATE SERVER to other nodes, since it might get run on a
>> non-BDR-enabled database.
>
> As Tom explained thats not the case for either of those two. To my
> knowledge the only reason those two commands aren't implemented is that
> either nobody implemented the required ddl deparsing or, actually
> somewhat likely, nobody removed the error check. Either way it should be
> simple to implement.

Well, that's embarrassing. Whoops. I could've sworn CREATE SERVER was global.

Thanks Tom and Andres for clearing up the misinformation.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Retrieve postgres password

[Adrian Klaver]

On 09/02/2015 04:43 PM, ray madigans.org wrote:

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.


Well in pg_hba.conf first match wins, so I would say you have a line the 
uses peer authentication before one that uses md5(password).


Can you show us the contents of the pg_hba.conf file?






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


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

[GENERAL] error on online backup using pg_basebackup tool

[Gerdan Rezende dos Santos]

Hi 4 all!!

As Ido not know if it is my mistake or actually a bug, sending the two
topics.

When trying to perform online backup using the pg_basebackup tool, I get
the message: The folder is not empty.

steps to reproduce the error:
1 - install postgres in the / usr / local / pgsql
2 - initialize the database: / usr / local / pgsql / bin / -D /data
3 - set up pg_hba.conf to allow replication (used by online backup with
pg_basebackup)
4 - create the directories /data/newtblspc and /data/pg_tblspc/newtblspc2
5 - start with postgres -D /data
6 - create table spaces:
CREATE TABLESPACE newtblspc LOCATION '/data/newtblspc';
CREATE TABLESPACE newtblspc2 LOCATION '/data/pg_tblspc/newtblspc2'
7 - mkdir /data2
8 - Now perform a backup using pg_basebackup -D /data2 and get the error
message.





-- 
T.'.A.'.F.'.,
*Gerdan Rezende dos Santos *
*Po*stgreSQL & EnterpriseDB Specialist, Support, Training & Services
+55 (61) 9645-1525

[GENERAL] [BUGS] error on online backup using pg_basebackup tool

[Gerdan Rezende dos Santos]

Hi 4 all!!

As Ido not know if it is my mistake or actually a bug, sending the two
topics.

When trying to perform online backup using the pg_basebackup tool, I get
the message: The folder is not empty.

steps to reproduce the error:
1 - install postgres in the / usr / local / pgsql
2 - initialize the database: / usr / local / pgsql / bin / -D /data
3 - set up pg_hba.conf to allow replication (used by online backup with
pg_basebackup)
4 - create the directories /data/newtblspc and /data/pg_tblspc/newtblspc2
5 - start with postgres -D /data
6 - create table spaces:
CREATE TABLESPACE newtblspc LOCATION '/data/newtblspc';
CREATE TABLESPACE newtblspc2 LOCATION '/data/pg_tblspc/newtblspc2'
7 - mkdir /data2
8 - Now perform a backup using pg_basebackup -D /data2 and get the error
message.


-- 
T.'.A.'.F.'.,
*Gerdan Rezende dos Santos *
*Po*stgreSQL & EnterpriseDB Specialist, Support, Training & Services
+55 (61) 9645-1525

Re: [GENERAL] get first and last row in one sql as two columns

[Tom Smith]

Thanks for suggestion.  I could use two separate sqls, but I am looking to
get it in a single sql.
I just compiled and installed (under postgres id)  first_last_agg C
extension.
http://pgxn.org/dist/first_last_agg/
it WORKED under postgres user with sample
select first(column1), last(column1) from (values (null),(1),(3),(null)) as
x
but somehow when I run under other users, it say

ERROR:  function first(integer) does not exist

below is the control file ,  I can not find out why I can not run under
other user ids.
Any help would be appreciated.

# first_last_agg extension
comment = 'first() and last() aggregate functions'
default_version = '0.1.4'
module_pathname = '$libdir/first_last_agg'
relocatable = false
schema = pg_catalog
superuser = false







On Wed, Sep 2, 2015 at 7:47 PM, Melvin Davidson 
wrote:

> Try this:
>
> SELECT
>   (SELECT 
>  FROM 
>  ORDER BY  offset 0 LIMIT 1) ,
>   (SELECT 
>  FROM 
>  ORDER BY  OFFSET (SELECT COUNT(*) ) LIMIT 1)
>FROM  LIMIT 1;
>
>
> On Wed, Sep 2, 2015 at 7:27 PM, Rob Sargent  wrote:
>
>> On 09/02/2015 05:14 PM, Tom Smith wrote:
>>
>> 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
>>
>> If you're ordering by col1, does
>>
>> select min(col1), max(col1) from table order by col1
>>
>> not do the trick;
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] [BUGS] error on online backup using pg_basebackup tool

[Michael Paquier]

On Thu, Sep 3, 2015 at 10:28 AM, Gerdan Rezende dos Santos
 wrote:
> 6 - create table spaces:
> CREATE TABLESPACE newtblspc LOCATION '/data/newtblspc';
> CREATE TABLESPACE newtblspc2 LOCATION '/data/pg_tblspc/newtblspc2'
> 7 - mkdir /data2
> 8 - Now perform a backup using pg_basebackup -D /data2 and get the error
> message.

You are performing a base backup on a node that has tablespaces on the
same hosts, so tablespaces are going to overlap. Isn't that your
problem? Note that 9.4 you can use tablespace-mapping to address the
problem, for older versions you are going to need some manual
operations before being able to run the standby and the master on the
same server.
-- 
Michael


-- 
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] error on online backup using pg_basebackup tool

[Gerdan Rezende dos Santos]

I use the tablespaces without problem but the backup online not
complete... but when I attempt to online backup using the pg_basebackup
displays the error message saying q pg_tblspc is not clean and the process
is terminated without sucess


Em quarta-feira, 2 de setembro de 2015, Michael Paquier <
michael.paqu...@gmail.com> escreveu:

> On Thu, Sep 3, 2015 at 10:28 AM, Gerdan Rezende dos Santos
> > wrote:
> > 6 - create table spaces:
> > CREATE TABLESPACE newtblspc LOCATION '/data/newtblspc';
> > CREATE TABLESPACE newtblspc2 LOCATION '/data/pg_tblspc/newtblspc2'
> > 7 - mkdir /data2
> > 8 - Now perform a backup using pg_basebackup -D /data2 and get the error
> > message.
>
> You are performing a base backup on a node that has tablespaces on the
> same hosts, so tablespaces are going to overlap. Isn't that your
> problem? Note that 9.4 you can use tablespace-mapping to address the
> problem, for older versions you are going to need some manual
> operations before being able to run the standby and the master on the
> same server.
> --
> Michael
>


-- 
T.'.A.'.F.'.,
*Gerdan Rezende dos Santos *
*Po*stgreSQL & EnterpriseDB Specialist, Support, Training & Services
+55 (61) 9645-1525

Re: [GENERAL] error on online backup using pg_basebackup tool

[Michael Paquier]

On Thu, Sep 3, 2015 at 12:20 PM, Gerdan Rezende dos Santos
 wrote:
>[...]

Note 1: Please don't top-post.
Note 2: Please don't post across multiple mailing lists.

> I use the tablespaces without problem but the backup online not complete...
> but when I attempt to online backup using the pg_basebackup displays the
> error message saying q pg_tblspc is not clean and the process is terminated
> without sucess.

That's what I am saying. You will need to redirect manually the soft
links of pg_tbspc into other paths if you want to run a standby on the
same host as the master.

That's not a bug, just an operation mistake.
-- 
Michael


-- 
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] error on online backup using pg_basebackup tool

[Gerdan Rezende dos Santos]

Em quinta-feira, 3 de setembro de 2015, Michael Paquier <
michael.paqu...@gmail.com> escreveu:

> On Thu, Sep 3, 2015 at 12:20 PM, Gerdan Rezende dos Santos
> > wrote:
> >[...]
>
> Note 1: Please don't top-post.
> Note 2: Please don't post across multiple mailing lists.
>
> > I use the tablespaces without problem but the backup online not
> complete...
> > but when I attempt to online backup using the pg_basebackup displays the
> > error message saying q pg_tblspc is not clean and the process is
> terminated
> > without sucess.
>
> That's what I am saying. You will need to redirect manually the soft
> links of pg_tbspc into other paths if you want to run a standby on the
> same host as the master.
>
> That's not a bug, just an operation mistake.
> --
> Michael
>


In other server with same configuration the proccess not complete too!


-- 
T.'.A.'.F.'.,
*Gerdan Rezende dos Santos *
*Po*stgreSQL & EnterpriseDB Specialist, Support, Training & Services
+55 (61) 9645-1525

[GENERAL] pg_basebackup problem for Streaming replication

[Sachin Srivastava]

Dear Experts,


One problem, I am facing that when I am using *pg_basebackup* (on Standby
Server) it’s copying only some files and directories not all like pg_notify
folder etc. That’s why when I am restarting the database on "Standby
Server" after "pg_basebackup" then it’s showing an error below. If you have
an idea why these files/folders(through pg_basebackup) are not copying
completely then tell to me.



*“2015-09-03 16:58:30 IST FATAL:  could not open directory "pg_notify": No
such file or directory”*



*Pg_basebackup command as below:*



*pg_basebackup -D /opt/PostgreSQL/9.1/data/ -v -Fp -l Testbackup -h
172.17.43.84 -U replication >& backup.log*



*Data directory of Standby server after taking the pg_basebackup through
above command*



[root@INN14U-DW1353 data]# pwd

/opt/PostgreSQL/9.1/data

[root@INN14U-DW1353 data]# ls -ltr

total 100

-rwx--. 1 postgres postgres   173 Sep  3 16:55 backup_label

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_log

drwx--. 2 postgres postgres  4096 Sep  3 16:55 global

drwx--. 5 postgres postgres  4096 Sep  3 16:55 base

-rwx--. 1 postgres postgres 19158 Sep  3 16:55 postgresql.conf_backup

-rwx--. 1 postgres postgres 19231 Sep  3 16:55 postgresql.conf

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_xlog

-rwx--. 1 postgres postgres 4 Sep  3 16:55 PG_VERSION

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_twophase

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_tblspc

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_subtrans

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_stat_tmp

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_serial

drwx--. 4 postgres postgres  4096 Sep  3 16:55 pg_multixact

-rwx--. 1 postgres postgres  4406 Sep  3 16:55 pg_hba.conf

drwx--. 2 postgres postgres  4096 Sep  3 16:55 main



*Data directory of “Primary Server”*





-bash-4.1$ pwd

/opt/PostgreSQL/9.1/data

-bash-4.1$ ls -ltr

total 124

-rw--- 1 postgres postgres 4 Aug 24 11:25 PG_VERSION

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_twophase

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_tblspc

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_subtrans

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_serial

drwx-- 4 postgres postgres  4096 Aug 24 11:25 pg_multixact

-rw--- 1 postgres postgres  1636 Aug 24 11:25 pg_ident.conf

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_clog

drwx-- 5 postgres postgres  4096 Aug 24 11:25 base

-rw-r--r-- 1 root root 19158 Aug 28 10:26 postgresql.conf_backup

-rw--- 1 root root  4222 Aug 28 10:37 pg_hba.conf_backup

drwx-- 2 postgres postgres  4096 Aug 31 13:19 main

-rw-rw-r-- 1 postgres postgres 19231 Sep  2 18:42 postgresql.conf

-rw--- 1 postgres postgres  4406 Sep  3 11:13 pg_hba.conf

-rw--- 1 postgres postgres73 Sep  3 11:13 postmaster.pid

-rw--- 1 postgres postgres65 Sep  3 11:13 postmaster.opts

drwx-- 2 postgres postgres  4096 Sep  3 11:13 pg_notify

drwxr-xr-x 2 postgres postgres  4096 Sep  3 11:13 pg_log

drwx-- 2 postgres postgres  4096 Sep  3 11:14 global

drwx-- 3 postgres postgres  4096 Sep  3 11:25 pg_xlog

drwx-- 2 postgres postgres  4096 Sep  3 11:42 pg_stat_tmp

-bash-4.1$


And one more thing when I am checking the pg_basebackup log then it's also
showing below error, why?


[root@INN14U-DW1353 ~]# cat backup.log


pg_basebackup: could not get WAL end position from server


Regards,

Sachin