Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread Alexander Farber 
And here is the table definition:

words=> \d words_games;
   Table "public.words_games"
  Column  |   Type   | Modifiers
--+--+---
 gid  | integer  | not null default
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone | not null
 finished | timestamp with time zone |
 player1  | integer  | not null
 player2  | integer  |
 played1  | timestamp with time zone |
 played2  | timestamp with time zone |
 score1   | integer  | not null
 score2   | integer  | not null
 hand1| character varying[]  | not null
 hand2| character varying[]  | not null
 pile | character varying[]  | not null
 letters  | character varying[]  | not null
 values   | integer[]| not null
 bid  | integer  | not null
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_notes" CONSTRAINT "words_notes_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread Alexander Farber 
Good morning,

it looks that I failed to provide sufficient information in the first mail,
sorry.

Here again my problem - here is my PHP script:

const SQL_GET_BOARD  = '
SELECT
out_bid AS bid,
out_letters AS letters,
out_values AS values
FROMwords_get_board(?)
';

try {
$dbh = pgsqlConnect();
$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values  = $row['values'];
}

} catch (PDOException $ex) {
exit('Database problem: ' . $ex);
}

function pgsqlConnect() {
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_CASE => PDO::CASE_LOWER);
return new PDO(sprintf('pgsql:host=%s;port=%u;dbname=%s', DBHOST,
DBPORT, DBNAME), DBUSER, DBPASS, $options);
}

Here I run it at psql 9.6.2:

words=> SELECT
out_bid AS bid,
out_letters AS letters,
out_values AS values
FROMwords_get_board(1)
;
 bid |


letters


 |


values



-+-
---
---
---
---
-+-
---
---
---
---
--
   1 |
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,К,О,Р,О,Б,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,Р,Е,Я,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,У,
NULL,NULL,П,Э,Р,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,С,П,И,Л,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,О,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,М,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Б,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,В,А,Н,Н,А,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL}} |
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL

[GENERAL] DISTINCT vs GROUP BY - was Re: is (not) distinct from

2017-03-02 Thread George Neuner 
On Wed, 01 Mar 2017 11:12:29 -0500, Tom Lane 
wrote:

>This is a great example of "select distinct" being used as a band-aid
>over a fundamental misunderstanding of SQL.  It's good advice to never use
>"distinct" unless you know exactly why your query is generating duplicate
>rows in the first place.

On that note:

I know most people here don't pay much - or any - attention to
SQLServer, however there was an interesting article recently regarding
significant performance differences between DISTINCT and GROUP BY as
used to remove duplicates.

https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct


Now I'm wondering if something similar might be lurking in Postgresql?

[Yeah, I know - test it and find out!  

Thing is, the queries used in the article are not simple.  Although
not explicitly stated, it hints that - at least for SQLServer - a
simple case involving a string column is probably insufficient, and
complex scenarios are required to produce significant differences.
]


I'll get around to doing some testing soon.  For now, I am just asking
if anyone has ever run into something like this?

George



-- 
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] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread rob stone 
Hi Alex,

On Thu, 2017-03-02 at 21:45 +0100, Alexander Farber wrote:
> Good evening!
> 
> I am calling this stored function - 
> 
> CREATE OR REPLACE FUNCTION words_get_board(
>                 in_gid integer
>         ) RETURNS TABLE (
>                 out_bid integer,
>                 out_letters varchar[15][15],
>                 out_values integer[15][15]
>         ) AS
> $func$
>         SELECT 
>                 bid,
>                 letters,
>                 values
>         FROM    words_games
>         WHERE   gid = in_gid;
> 
> $func$ LANGUAGE sql STABLE;
> 
> by a PHP script -
> 
>         $sth = $dbh->prepare(SQL_GET_BOARD);
>         $sth->execute(array($gid));
>         if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
>                 $bid     = $row['bid'];
>                 $letters = $row['letters'];
>                 $values  = $row['values'];
>         }
> 
> 

Don't use PDO but assuming you want to loop through a result set,
shouldn't this line:-

if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {

be

> while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {

Also you need to test if the result set is empty or not.

I don't have an "easy" way to convert Postgres arrays into PHP style
arrays. What I ended up doing was to use the array_to_string function
in the select statement using the underscore character as the delimiter
in the string, and then converting the string to a PHP array via an
explode. However, I've only had to do this with single level arrays.

Don't know if this helps as you haven't supplied the table definition
of words_games.

Cheers,
Rob



-- 
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] Postgres 9.x table/index stats reset timestamp

2017-03-02 Thread Michael Paquier 
On Fri, Mar 3, 2017 at 3:03 AM, Dave Quello  wrote:
> Function pg_stat_reset_single_table_counters() resets stats for a single
> table/index that you typically query through pg_stat_user_tables or
> pg_stat_user_indexes, but there is no apparent way to retrieve the timestamp
> of the reset.  All stats in the db can be reset using pg_stat_reset() and
> the last reset date can be retrieved from pg_stat_database, but I’d prefer
> not to reset ALL stats when the focus is on a particular table/index.
>
> Are individual table/index reset timestamps stored?  If so, can they be
> retrieved?

The reset timestamp of each table entry is unfortunately not stored,
look at PgStat_StatTabEntry to see what's stored. Well we could have
that as well, it is important to be careful that the reset is updated
for all tables. Note that the timestamp reset happens in pgstat.c
actually. What you could do for now is to use a wrapper on top of
pg_stat_reset_single_table_counters() that calls now() and stores
timestamp on a custom table storing (oid,timestamp).
-- 
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] PG on SSD

2017-03-02 Thread Scott Marlowe 
On Thu, Mar 2, 2017 at 12:42 PM, scott ribe  wrote:
> Is it reasonable to run PG on a mirrored pair of something like the Intel SSD 
> DC 3610 series? (For example:
> http://ark.intel.com/products/82935/Intel-SSD-DC-S3610-Series-480GB-2_5in-SATA-6Gbs-20nm-MLC)
>  I'd *hope* that anything Intel classifies as a "Data Center SSD" would be 
> reasonably reliable, have actually-working power loss protection etc, but is 
> that the case?


From the spec sheet they certainly seem to be safe against power loss.
I'd still test by pulling the power cables while running benchmarks to
be sure.

I've used the other Intel enterprise class ssds with good results on
the power plug pull tests.


-- 
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] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread Adrian Klaver 

On 03/02/2017 01:30 PM, Alexander Farber wrote:

Adrian, but the stored function works, I am just not happy that the
results are casted to strings by PHP... and wonder hpw to fix or
workaround this.


So what is the result when you run the function in psql?

Also if I am following correctly should this:

  $bid = $row['bid'];
$letters = $row['letters'];
$values  = $row['values']

not be?:

  $bid = $row['out_bid'];
$letters = $row['out_letters'];
$values  = $row['out_values']






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

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread Thomas Kellerer 

Alexander Farber schrieb am 02.03.2017 um 21:45:

I am calling this stored function -

CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROMwords_games
WHERE   gid = in_gid;

$func$ LANGUAGE sql STABLE;

by a PHP script -

$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values  = $row['values'];
}


What exactly is the query in SQL_GET_BOARD?
How exactly are you calling that function?






--
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] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread Alexander Farber 
Yes, as David notices it is SQL function and not pg/PlSQL (you have
probably misread this).

I wonder what to do with the string in PHP, how to convert it to an
(2-dimensional) array.

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread Alexander Farber 
Adrian, but the stored function works, I am just not happy that the results
are casted to strings by PHP... and wonder hpw to fix or workaround this.

>
>

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread David G. Johnston 
On Thu, Mar 2, 2017 at 2:12 PM, Adrian Klaver 
wrote:

> On 03/02/2017 12:45 PM, Alexander Farber wrote:
>
>> Good evening!
>>
>> I am calling this stored function -
>>
>> CREATE OR REPLACE FUNCTION words_get_board(
>> in_gid integer
>> ) RETURNS TABLE (
>> out_bid integer,
>> out_letters varchar[15][15],
>> out_values integer[15][15]
>> ) AS
>> $func$
>> SELECT
>> bid,
>> letters,
>> values
>> FROMwords_games
>> WHERE   gid = in_gid;
>>
>> $func$ LANGUAGE sql STABLE;
>>
>
> I am not seeing where you assign the results of your SELECT to the OUT
> parameters in the RETURNS TABLE. Nor do I see anything that turns the
> results into an array for those OUT parameters of array type.
>
>
​The function isn't the problem here - its in SQL language.  Explicit
assignment is needed in pl/pgsql​.


>
>
>> by a PHP script -
>>
>> $sth = $dbh->prepare(SQL_GET_BOARD);
>> $sth->execute(array($gid));
>> if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
>> $bid = $row['bid'];
>> $letters = $row['letters'];
>> $values  = $row['values'];
>> }
>>
>> And then print the variable type -
>>
>> error_log('letters: ' . gettype($letters));
>>
>> and it is a "string" (instead of inspected array) with the following
>> content:
>>
>
Proper usage of PHP PDO is the concern - and outside my personal experience.

David J.

[GENERAL] PG on SSD

2017-03-02 Thread scott ribe 
Is it reasonable to run PG on a mirrored pair of something like the Intel SSD 
DC 3610 series? (For example: 
http://ark.intel.com/products/82935/Intel-SSD-DC-S3610-Series-480GB-2_5in-SATA-6Gbs-20nm-MLC)
 I'd *hope* that anything Intel classifies as a "Data Center SSD" would be 
reasonably reliable, have actually-working power loss protection etc, but is 
that the case?




-- 
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] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread Adrian Klaver 

On 03/02/2017 12:45 PM, Alexander Farber wrote:

Good evening!

I am calling this stored function -

CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROMwords_games
WHERE   gid = in_gid;

$func$ LANGUAGE sql STABLE;


I am not seeing where you assign the results of your SELECT to the OUT 
parameters in the RETURNS TABLE. Nor do I see anything that turns the 
results into an array for those OUT parameters of array type.




by a PHP script -

$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values  = $row['values'];
}

And then print the variable type -

error_log('letters: ' . gettype($letters));

and it is a "string" (instead of inspected array) with the following
content:

[02-Mar-2017 21:28:33 Europe/Berlin] letters: string
[02-Mar-2017 21:28:33 Europe/Berlin] letters:
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,К,А,Й,Т,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Ь,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Е,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}

How to handle this please?

I was hoping to fetch a PHP array and process it with "foreach" in my
PHP-script.

Thank you
Alex





--
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] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

2017-03-02 Thread Alexander Farber 
Good evening!

I am calling this stored function -

CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROMwords_games
WHERE   gid = in_gid;

$func$ LANGUAGE sql STABLE;

by a PHP script -

$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values  = $row['values'];
}

And then print the variable type -

error_log('letters: ' . gettype($letters));

and it is a "string" (instead of inspected array) with the following
content:

[02-Mar-2017 21:28:33 Europe/Berlin] letters: string
[02-Mar-2017 21:28:33 Europe/Berlin] letters:
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,К,А,Й,Т,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Ь,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Е,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}

How to handle this please?

I was hoping to fetch a PHP array and process it with "foreach" in my
PHP-script.

Thank you
Alex

[GENERAL] Postgres 9.x table/index stats reset timestamp

2017-03-02 Thread Dave Quello 
Hi,


Function *pg_stat_reset_single_table_counters*() resets stats for a single
table/index that you typically query through pg_stat_user_tables or
pg_stat_user_indexes, but there is no apparent way to retrieve the
timestamp of the reset.  All stats in the db can be reset using
pg_stat_reset() and the last reset date can be retrieved from
pg_stat_database, but I’d prefer not to reset ALL stats when the focus is
on a particular table/index.


Are individual table/index reset timestamps stored?  If so, can they be
retrieved?

Thanks,
Dave

Re: [GENERAL] pg_restore successful with warnings returns exit code of non-zero

2017-03-02 Thread Adrian Klaver 

On 03/02/2017 07:39 AM, dhanuj hippie wrote:

I have a pg dump (custom format, column-inserts) which I'm restoring on
an existing DB as
" /usr/bin/pg_restore -a -v -d db1 -F c " and the input is passed in
from stdin. It warns on a couple of existing rows, but completes
successfully with "WARNING: errors ignored on restore: 5".
However, this returns a exit code of 1 eventhough the command is run
fine and data is restored. Is there a way to tell pg_restore to return 0
in this case ?


Actually the command did not run fine, it threw 5 errors. Now they maybe 
harmless errors or they may not be, that should be for the user to 
determine after examining them.




Thanks



--
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] pg_restore successful with warnings returns exit code of non-zero

2017-03-02 Thread dhanuj hippie 
I have a pg dump (custom format, column-inserts) which I'm restoring on an
existing DB as
" /usr/bin/pg_restore -a -v -d db1 -F c " and the input is passed in from
stdin. It warns on a couple of existing rows, but completes successfully
with "WARNING: errors ignored on restore: 5".
However, this returns a exit code of 1 eventhough the command is run fine
and data is restored. Is there a way to tell pg_restore to return 0 in this
case ?

Thanks

Re: [GENERAL] column "waiting" does not exist

2017-03-02 Thread Johann Spies 
Thanks Charles and Neslişah.


Charles Clavadetscher   wrote:

The name of the column in pg_stat_activity has changed. I assume it comes
from there?


On 2 March 2017 at 10:54, Neslisah Demirci 
wrote:


> Your monitoring program is trying to use pg_stat_activity's old version.
> In 9.6 pg_stat_activity has new columns named wait_event , wait_event_type
> .
>
> Which monitoring programme?
>
 Munin is the culprit.  We will update it soon.

Regards
Johann



-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: [GENERAL] Setup pgpool-II with streaming replication

2017-03-02 Thread subash 
Hi Ashish,

could you please help me setting up the pgpool , i am currenlty into same
like u had asked to setup .

I have mater/slave 9.4 and i would need to setup pgpool so that app connects
to pgpool and diverts the connection to new master .

Could u please help me with ur thoughts?

Thanks,
Subash



--
View this message in context: 
http://www.postgresql-archive.org/Setup-pgpool-II-with-streaming-replication-tp5920011p5947166.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] appropriate column for storing ipv4 address

2017-03-02 Thread Emre Hasegeli 
> Maybe it will be useful for you! Or maybe there is already some built-in way
> to treat cidr columns like ranges?

There is GiST operator class since version 9.4 and SP-GiST operator
class on version 9.6:

CREATE INDEX ON tracked_ip_address USING gist (ip_address inet_ops);

CREATE INDEX ON tracked_ip_address USING spgist (ip_address);

Performance would change depending on the dataset.  I am more
confident from SP-GiST one.


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

Ynt: [GENERAL] column "waiting" does not exist

2017-03-02 Thread Neslisah Demirci 
Hi ,


Your monitoring program is trying to use pg_stat_activity's old version. In 9.6 
pg_stat_activity has new columns named wait_event , wait_event_type .


Which monitoring programme?


Neslişah Demirci | Veritabanı Yöneticisi

Ayazağa cad. No:4 Uniq İstanbul Plaza
B2 /Kat:3 34396 Ayazağa-SARIYER-İstanbul
T. (+90) 212 453 16 00 – 5516
F. (+90) 212 453 16 16
www.markafoni.com
www.facebook.com/markafoni
blog.markafoni.com





Gönderen: Johann Spies  adına 
pgsql-general-ow...@postgresql.org 
Gönderildi: 2 Mart 2017 Perşembe 11:27
Kime: pgsql-general@postgresql.org
Konu: [GENERAL] column "waiting" does not exist

I have no idea what is causing this message in the logs (PostgreSQL 
9.6+177.pgdg80+1 on Debian):

2017-03-02 10:20:25 SAST [5196-1] [unknown] postgres postgres@template1 ERROR:  
column "waiting" does not exist at character 217
2017-03-02 10:20:25 SAST [5196-2] [unknown] postgres postgres@template1 
STATEMENT:  SELECT tmp.mstate AS state,COALESCE(count,0) FROM
 (VALUES 
('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate)
LEFT JOIN
 (SELECT CASE WHEN waiting THEN 'waiting' WHEN 
state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idle$
 count(*) AS count
 FROM pg_stat_activity WHERE pid != pg_backend_pid()  
AND datname='data_portal'
 GROUP BY CASE WHEN waiting THEN 'waiting' WHEN 
state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idl$
 ) AS tmp2
ON tmp.mstate=tmp2.mstate
ORDER BY 1;

I do not know whether it is related but we recently get warnings about bloat in 
our system tables from the monitoring program.

How do I fix the cause of this error message?

Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: [GENERAL] column "waiting" does not exist

2017-03-02 Thread Charles Clavadetscher 
Hello

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Johann Spies
Sent: Donnerstag, 2. März 2017 09:28
To: pgsql-general@postgresql.org
Subject: [GENERAL] column "waiting" does not exist

I have no idea what is causing this message in the logs (PostgreSQL 
9.6+177.pgdg80+1 on Debian):


2017-03-02 10:20:25 SAST [5196-1] [unknown] postgres postgres@template1 ERROR:  
column "waiting" does not exist at character 217
2017-03-02 10:20:25 SAST [5196-2] [unknown] postgres postgres@template1 
STATEMENT:  SELECT tmp.mstate AS state,COALESCE(count,0) FROM
 (VALUES 
('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate)
LEFT JOIN
 (SELECT CASE WHEN waiting THEN 'waiting' WHEN 
state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idle$
 count(*) AS count
 FROM pg_stat_activity WHERE pid != pg_backend_pid()  
AND datname='data_portal'
 GROUP BY CASE WHEN waiting THEN 'waiting' WHEN 
state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idl$
 ) AS tmp2
ON tmp.mstate=tmp2.mstate
ORDER BY 1;
I do not know whether it is related but we recently get warnings about bloat in 
our system tables from the monitoring program.
How do I fix the cause of this error message?

The name of the column in pg_stat_activity has changed. I assume it comes from 
there?:

9.3:

   View "pg_catalog.pg_stat_activity"
  Column  |   Type   | Modifiers
--+--+---
 datid| oid  |
 datname  | name |
 pid  | integer  |
 usesysid | oid  |
 usename  | name |
 application_name | text |
 client_addr  | inet |
 client_hostname  | text |
 client_port  | integer  |
 backend_start| timestamp with time zone |
 xact_start   | timestamp with time zone |
 query_start  | timestamp with time zone |
 state_change | timestamp with time zone |
 waiting  | boolean  | <---
 state| text |
 query| text |

9.6:

   View "pg_catalog.pg_stat_activity"
  Column  |   Type   | Modifiers
--+--+---
 datid| oid  |
 datname  | name |
 pid  | integer  |
 usesysid | oid  |
 usename  | name |
 application_name | text |
 client_addr  | inet |
 client_hostname  | text |
 client_port  | integer  |
 backend_start| timestamp with time zone |
 xact_start   | timestamp with time zone |
 query_start  | timestamp with time zone |
 state_change | timestamp with time zone |
 wait_event_type  | text | <---
 wait_event   | text | <---
 state| text |
 backend_xid  | xid  |
 backend_xmin | xid  |
 query| text |

Regards
Charles

Regards
Johann
-- 
Because experiencing your loyal love is better than life itself, 
my lips will praise you.  (Psalm 63:3)



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

[GENERAL] column "waiting" does not exist

2017-03-02 Thread Johann Spies 
I have no idea what is causing this message in the logs (PostgreSQL
9.6+177.pgdg80+1 on Debian):

2017-03-02 10:20:25 SAST [5196-1] [unknown] postgres postgres@template1
ERROR:  column "waiting" does not exist at character 217
2017-03-02 10:20:25 SAST [5196-2] [unknown] postgres postgres@template1
STATEMENT:  SELECT tmp.mstate AS state,COALESCE(count,0) FROM
 (VALUES
('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS
tmp(mstate)
LEFT JOIN
 (SELECT CASE WHEN waiting THEN 'waiting' WHEN
state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idle$
 count(*) AS count
 FROM pg_stat_activity WHERE pid !=
pg_backend_pid()  AND datname='data_portal'
 GROUP BY CASE WHEN waiting THEN 'waiting' WHEN
state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idl$
 ) AS tmp2
ON tmp.mstate=tmp2.mstate
ORDER BY 1;

I do not know whether it is related but we recently get warnings about
bloat in our system tables from the monitoring program.

How do I fix the cause of this error message?

Regards
Johann
-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)