Re: [GENERAL] Setting search_path ignored

[Charles Clavadetscher]

Hello

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guyren Howe
Sent: Montag, 2. Oktober 2017 16:10
To: David G. Johnston 
Cc: PostgreSQL General 
Subject: Re: [GENERAL] Setting search_path ignored

 

I logged out and back and did SET ROLE and got the same resullt.


On Oct 2, 2017, 10:06 -0400, David G. Johnston  >, wrote:



On Mon, Oct 2, 2017 at 7:00 AM, Guyren Howe  > wrote:

CREATE ROLE thing_accessor;

CREATE ROLE

CREATE SCHEMA thing_accessor;

CREATE SCHEMA

covermything=> ALTER ROLE thing_accessor SET search_path=thing_accessor;

ALTER ROLE

covermything=# SET ROLE thing_accessor;

SET

covermything=> SHOW search_path;

   search_path   

-

 "$user", public

(1 row)

 

This seems to contradict all the advice I can find about setting the schema 
search path.

 

​Settings associated with roles only take affect at session start/login.   SET 
ROLE does not cause them to be read/executed.

 

  
https://www.postgresql.org/docs/9.6/static/config-setting.html

 

"Values set with ALTER DATABASE and ALTER ROLE are applied only when starting a 
fresh database session."

 

David J.

​

 

search_path is not a privilege, but a property.

Properties are tight to the logged in user. With SET ROLE you become the role 
only for its privileges, not its properties.

 

Regards

Charles

 

Re: [GENERAL] PostgreSQL COPY Statement Error On Linux

[Charles Clavadetscher]

Hello

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Osahon Oduware
Sent: Dienstag, 12. September 2017 12:30
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostgreSQL COPY Statement Error On Linux

 

Hi All,

I am trying to utilize the "COPY" statement below to copy a .CSV file to a 
table in a PostgreSQL database.:
COPY .() FROM 
'\\shared\network\path\to\csv\test.csv' DELIMITER ',' CSV HEADER;

This works with a PostgreSQL database installed in a WINDOWS environment 
(Windows 7), but fails with the following error with a similar PostgreSQL 
database in a Linux environment (Centos 7):
org.postgresql.util.PSQLException: ERROR: could not open file 
"\\shared\network\path\to\csv\test.csv 
 " for reading: No such file or 
directory



It looks like the share is not visible for the Linux system. You probably need 
to mount it first using Samba and then access it through the mount point using 
slashes instead of bakslashes: /

Instructions on how to mount a Windows share in Linux can be found on the 
internet. Since I am not an expert on this myself, I can’t give you more 
concrete instructions.

 

This may help: 
http://www.serverlab.ca/tutorials/linux/storage-file-systems-linux/mounting-smb-shares-centos-7/

 

An alternative would be to copy the file to the Linux system using e.g. scp of 
sftp and the load it locally.

 

Hope this helps.

Bye

Charles


I have granted READ access to EVERYONE on the CSV folder on the network path as 
depicted in the attached image. 

Could someone point me to the reason for the failure in Linux?

Re: [GENERAL] Issue with json_agg() and ordering

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Jones
> Sent: Freitag, 1. September 2017 10:12
> To: pgsql-general 
> Subject: [GENERAL] Issue with json_agg() and ordering
> 
> Hi,
> 
> 
> Could anyone give me a few pointers as to how I might resolve the following :
> 
> select json_agg(my_table) from (my_table) where foo='test' and bar='f'
> order by last_name asc, first_name asc;
> 
> ERROR:  column "my_table.last_name" must appear in the GROUP BY clause or be 
> used in an aggregate function LINE 1:
> ...foo='f' order by last_name ...

I guess that the order by should be in the aggregation.

SELECT json_agg(a.* ORDER BY a.last_name, a.last_year DESC)
FROM my_table a;

Regards
Charles

> 
> 
> I suspect It doesn't really matter what my table looks like for the purposes 
> of the above, but if you need something
> to go by:
> 
> create table my_table(
> last_name text,
> first name text,
> foo text,
> bar boolean
> );
> 
> Thanks !
> 
> Bob
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] Table create time

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of hamann.w@t-
> online.de
> Sent: Donnerstag, 31. August 2017 08:56
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Table create time
> 
> 
> Hi,
> 
> is there a way to add a table create (and perhaps schema modify) timestamp to 
> the system?
> I do occasionally create semi-temporary tables (meant to live until a problem 
> is solved, i.e. longer than a session)
> with conveniently short names.

Unfortunately I can't help with this.

> Also, is there a simple query to identify tables without a table comment? (so 
> a weekly cron could remind me of
> tables that are already a few days old but have no explanatory comment)

Comments are stored in pg_catalog.pg_description. If there is an entry for your 
table then it has comments, otherwise not.

Example:

SELECT * FROM pg_description WHERE objoid = to_regclass('survey_control');

 objoid  | classoid | objsubid |
description
-+--+--+---
 2523030 | 1259 |0 | Main table for survey management and control.
 2523030 | 1259 |1 | The name of the survey.
 2523030 | 1259 |2 | The year of conduction.
[...]

For tables the objsubid is 0 for the table comment and 1..n for column 
description. The number refers to the position of the column in the table.

Hope this helps.

Regards
Charles

> 
> I am running PG 9.3
> 
> Best regards
> Wolfgang Hamann
> 
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] 'value too long' and before insert/update trigger

[Charles Clavadetscher]

Hi

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Kevin Golding
> Sent: Donnerstag, 24. August 2017 07:08
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] 'value too long' and before insert/update trigger
> 
> Hi all
> I'm currently migrating a legacy Informix 4gl application to run on 
> PostgreSQL (v9.5.8)
> 
> There are errors occurring because sometimes the application tries to 
> insert/update values longer than the
> definition of the database column.
> The error message is eg. "value too long for type character(20)".
> 
> The behaviour under Informix is to silently truncate the saved value, but 
> with PostgreSQL it crashes our application
> program. Obviously the short answer is to modify the application so that it 
> does not attempt to save values that are
> too long, but with 1000+ programs and approx. 8000 insert/update statements 
> to review this will take a lot of time.

If there is no good reason to force the length of the field to be a maximum of 
20 characters, you may change the data type to text.

> I was hoping to emulate the Informix behaviour by creating triggers to run 
> before each insert/update that would
> check the length of the values being saved and truncate if too long. However 
> I'm still getting the error messages.
> Presumably the length validation is being done before the trigger is run. Is 
> there some way this could be changed so
> the trigger happens first?

When you create the trigger you can define if the function must be called 
before or after the action.

Something like:

CREATE TRIGGER name
BEFORE INSERT OR UPDATE ON table_name
FOR EACH ROW EXECUTE PROCEDURE ...;

In the function you must then replace the value of the string before you return 
the NEW record to the calling process:

[...]

NEW.variable_name := substr(NEW.variable_name,1,20);

[...]

RETURN NEW;

Hope this helps.
Bye
Charles

> I've seen examples that involve changing the char type columns to text, but 
> this changes the semantics of string
> comparisons with regard to trailing spaces, and I'm concerned that this might 
> cause subtle and hard to find problems
> within the application.
> 
> Thanks
> Kevin
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] COPY: row is too big

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of doganmeh
> Sent: Freitag, 26. Mai 2017 14:08
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] COPY: row is too big
> 
> I am piggy-backing in this thread because I have the same issue as well. I 
> need to import a csv file that is 672
> columns long and each column consists of 12 alpha-numeric characters. Such as:
> 
> SA03ARE1015D  SA03ARE1S15NSB03ARE1015D  ...
> 356412275812  43106  ...
> 
> I am aware this is not normalized, however, we (or try to) keep source data 
> intact, and normalize after importing
> into our system.
> 
> While trying to import all columns to type `text` I get this error:
> 
> [54000] ERROR: row is too big: size 8760, maximum size 8160
> Where: COPY temp_table, line 3
> SQL statement "copy temp_table from
> '/home/edgleweb/data/raw/TX/TAPR/2015/ADV/SSTAAR1ADV.csv' with delimiter ','
> quote '"' csv "

Is the delimiter really ','? In the lines above it seems to be different.
Did you check line 3?

Regards,
Charles

> I tried varchar(12) also, nothing changed. My questions is 1) I have
> 672x12=8,064 characters in the first row (which are actually the headers), 
> why would it complain that it is 8760. I
> am assuming here type `text` occupies 1 byte for a character. 2) Is there 
> anything I can do to work around this
> situation?
> 
> Thanks in advance.
> 
> 
> 
> --
> View this message in context: 
> http://www.postgresql-archive.org/COPY-row-is-too-big-tp5936997p5963385.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



-- 
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] Wiki editor privilege

[Charles Clavadetscher]

Thanks!

On 03/23/2017 10:11 AM, Magnus Hagander wrote:
> On Thu, Mar 23, 2017 at 7:57 AM, Charles Clavadetscher
> <clavadetsc...@swisspug.org <mailto:clavadetsc...@swisspug.org>> wrote:
> 
> Hello
> 
> I would like to upload the slides of my presentation at Nordic PGDay
> 2017. Could you please grant me edit privilege on the wiki?
> 
> My user name is cclavadetscher
> 
> 
> Hi!
> 
> Done. 
> 
> -- 
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/

-- 
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PGDay 2017  |
| HSR Rapperswil 30.06.2017 |
|http://www.pgday.ch|
|   |
+---+


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

[GENERAL] Wiki editor privilege

[Charles Clavadetscher]

Hello

I would like to upload the slides of my presentation at Nordic PGDay
2017. Could you please grant me edit privilege on the wiki?

My user name is cclavadetscher

Thank you and bye
Charles

-- 
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PGDay 2017  |
| HSR Rapperswil 30.06.2017 |
|http://www.pgday.ch|
|   |
+---+


-- 
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] How to define the limit length for numeric type?

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of vod vos
> Sent: Montag, 13. März 2017 15:52
> To: rob stone 
> Cc: pgsql-general 
> Subject: Re: [GENERAL] How to define the limit length for numeric type?
> 
> Sorry for taking varchar(4) as an example, What I want urgently is how to 
> make a constraint of fixed length of a
> numeric data, that you can only input data like 23.45,  and if you input the 
> data like 2.45, 23.4356, 233.45, you
> will get a warning message from postgresql.
> 
> I think expr will do the job, but are there any simpler ways to do it in 
> postgresql?

Well, I don't think that you will find anything simpler than using a regexp in 
a check constraint, as Tom and I did suggest.

https://www.postgresql.org/message-id/15358.1489336741%40sss.pgh.pa.us
https://www.postgresql.org/message-id/040301d29b01%2443d71f50%24cb855df0%24%40swisspug.org

I have some trouble understanding what you find so complicated in that solution?

Bye
Charles

> 
> 
>   On 星期日, 12 三月 2017 14:28:53 -0700 rob stone  
> wrote   > Hello,  >  > On Sat, 2017-
> 03-11 at 22:14 -0800, vod vos wrote:
>  > > Hi everyone,
>  > >
>  > > How to define the exact limit length of numeric type? For example,  > >  
> > > CREATE TABLE test  (id serial,
> goose numeric(4,1));  > >  > > 300.2 and 30.2 can be inserted into COLUMN 
> goose, but I want 30.2 or  > > 3.2 can not
> be inserted, how to do this?
>  > >
>  > > Thank you.
>  > >
>  > >
>  > >
>  >
>  >
>  > Assuming that column goose may only contain values ranging from 100.0  > 
> to 999.9, then a check constraint along
> the lines of:-  >  > goose > 99.9 and < 1000  >  > should do the trick.
>  >
>  > HTH,
>  > Rob
>  >
>  >
>  > --
>  > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)  > To 
> make changes to your subscription:
>  > http://www.postgresql.org/mailpref/pgsql-general
>  >
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] How to define the limit length for numeric type?

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of vod vos
> Sent: Sonntag, 12. März 2017 08:01
> To: Pavel Stehule 
> Cc: pgsql-general 
> Subject: Re: [GENERAL] How to define the limit length for numeric type?
> 
> Maybe CHECK (goose >= 100 AND goose <= -100)  works better, But if :
> 
> INSERT INTO test VALUES (1, 59.2);
> INSERT INTO test VALUES (1, 59.24);
> INSERT INTO test VALUES (1, 59.26);
> INSERT INTO test VALUES (1, 59.2678);
> 
> The INSERT action still can be done. What I want is just how to limit the 
> length of the insert value, you can just
> type format like 59.22, only four digits length.

You may change (or extend) the CHECK condition using regexp:

SELECT 59.2::TEXT ~ '^[0-9]+\.[0-9]{2}$';
 ?column?
--
 f

SELECT 59.24::TEXT ~ '^[0-9]+\.[0-9]{2}$';
 ?column?
--
 t

SELECT 59.26::TEXT ~ '^[0-9]+\.[0-9]{2}$';
 ?column?
--
 t

SELECT 59.2678::TEXT ~ '^[0-9]+\.[0-9]{2}$';
 ?column?
--
 f

Of course you can change the part left of the dot to also be limited to 2 
digits.

Regards
Charles

> 
> Thank you.
> 
> 
>   On 星期六, 11 三月 2017 22:25:19 -0800 Pavel Stehule 
>  wrote   >  >  > 2017-03-12
> 7:14 GMT+01:00 vod vos :
>  >
>  >  Hi everyone,
>  >
>  >  How to define the exact limit length of numeric type? For example,  >  >  
> CREATE TABLE test  (id serial, goose
> numeric(4,1));  >  >  300.2 and 30.2 can be inserted into COLUMN goose, but I 
> want 30.2 or 3.2 can not be inserted,
> how to do this?
>  >
>  > ostgres=# CREATE TABLE test  (id serial, goose numeric(4,1));CREATE 
> TABLETime: 351,066 mspostgres=# insert into
> test values(1,3.2);INSERT 0 1Time: 65,997 mspostgres=# select * from 
> test;┌┬───┐│ id │ goose
> │╞╪═══╡│  1 │   3.2 │└┴───┘(1 row)Time: 68,022 ms
>  > Regards
>  > Pavel
>  >  Thank you.
>  >
>  >
>  >
>  >  --
>  >  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)  >  To 
> make changes to your subscription:
>  >  http://www.postgresql.org/mailpref/pgsql-general
>  >
>  >
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] How to define the limit length for numeric type?

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of vod vos
> Sent: Sonntag, 12. März 2017 07:15
> To: pgsql-general 
> Subject: [GENERAL] How to define the limit length for numeric type?
> 
> 
> Hi everyone,
> 
> How to define the exact limit length of numeric type? For example,
> 
> CREATE TABLE test  (id serial, goose numeric(4,1));
> 
> 300.2 and 30.2 can be inserted into COLUMN goose, but I want 30.2 or 3.2 can 
> not be inserted, how to do this?

Maybe with a CHECK constraint?

CREATE TABLE test
(
  id serial,
  goose numeric(4,1),
  CHECK (goose > 30.2)
);

INSERT INTO test (goose) VALUES (300.2);
INSERT 0 1

INSERT INTO test (goose) VALUES (30.2);
ERROR:  new row for relation "test" violates check constraint "test_goose_check"
DETAIL:  Failing row contains (2, 30.2).

Of course you should set the correct value that you want to use in the 
contraint definition.

Regards
Charles

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



-- 
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] Fwd: parameter type is unknown error

[Charles Clavadetscher]

Hallo Adrian

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver
> Sent: Montag, 6. März 2017 15:13
> To: Günce Kaya <guncekay...@gmail.com>; Charles Clavadetscher 
> <clavadetsc...@swisspug.org>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Fwd: parameter type is unknown error
> 
> On 03/06/2017 04:36 AM, Günce Kaya wrote:
> > Hi Charles,
> >
> > You are right about parameter types. When I use function with cast as
> > you mentioned, I don't see "unknown". I see data types. but still
> > doesn't insert with my custom function. It's really weird cause I did
> > same thing for different function and It works.
> 
> If I am following correctly could not the function be eliminated by using?:
> 
> insert into cargo.invoice ( orderid,  ) RETURNING id;

Yes, of course. I assume that this was a simplified example from the OP.
Bye
Charles

> > Regards,
> >
> > Gunce
> 
> --
> 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



-- 
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] Fwd: parameter type is unknown error

[Charles Clavadetscher]

Hello Günce

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Günce Kaya
> Sent: Montag, 6. März 2017 13:37
> To: Charles Clavadetscher <clavadetsc...@swisspug.org>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Fwd: parameter type is unknown error
> 
> Hi Charles,
> 
> You are right about parameter types. When I use function with cast as you 
> mentioned, I don't see "unknown". I see
> data types. but still doesn't insert with my custom function. It's really 
> weird cause I did same thing for different
> function and It works.

This works:

CREATE TABLE cargo.invoice
(
  id bigserial NOT NULL,
  orderid integer NOT NULL,
  cargoid integer NOT NULL,
  invoiceowner integer NOT NULL,
  invoiceaddress character(250) NOT NULL,
  receiverfname character varying(50) NOT NULL,
  receiverlname character varying(50) NOT NULL,
  receiverphone integer NOT NULL,
  sendingdatetime timestamp without time zone DEFAULT now(),
  distance real NOT NULL,
  weight numeric NOT NULL,
  addedtax numeric NOT NULL DEFAULT 8,
  invoiceamount money DEFAULT 0,
  cargocreateddate date,
  cargoupdateddate timestamp without time zone,
  cargocancelled timestamp without time zone
);

create or replace function cargo.insertinvoice (forderid integer,
fcargoid integer,
finvoiceowner integer,
finvoiceaddress varchar(250),
freceiverfname varchar,
freceiverlname varchar,
freceiverphone integer,
fsendingdatetime timestamp 
without time zone,
fdistance real,
faddedtax numeric,
fweight numeric,
finvoiceamount money,
fcargocreateddate date,
fcargoupdateddate timestamp,
fcargocancelled timestamp 
without time zone) returns numeric as $$
declare v_id bigint;
begin

insert into cargo.invoice (orderid,
   cargoid,
   invoiceowner,
   invoiceaddress,
   receiverfname,
   receiverlname,
   receiverphone,
   sendingdatetime,
   distance,
   weight,
   addedtax,
   invoiceamount,
   cargocreateddate,
   cargoupdateddate,
   cargocancelled) 
values(forderid,
   fcargoid,
   finvoiceowner,
   finvoiceaddress,
   freceiverfname,
   freceiverlname,
   freceiverphone,
   fsendingdatetime,
   fdistance,
   fweight,
   faddedtax,
   finvoiceamount,
   fcargocreateddate,
   fcargoupdateddate,
   fcargocancelled)
returning id into v_id;
return v_id;
end;
$$ language plpgsql;

select * from cargo.insertinvoice(1013,
  10,
  44,
  'Wherever'::varchar(250),
  'test1'::varchar,
  'test2'::varchar,
  12345,
  now()::timestamp,
  1.30::real,
  1000::numeric,
  8::numeric,
  400::money,
  now()::date,
  now()::timestamp,
  now()::timestamp);

As Tom pointed out there were many problems:
- List of parameter not matching the table.
- List of parameters types not matching the list in the function call.
- Returning the id from the function.

Some of the casting may not be mandatory, but it is up to you now to check this.

Hope this helps.
Bye
Charles

> 
> Regards,
> 
> Gunce
> 
> 2017-03-06 15:22 GMT+03:00 Charles Clavadetscher <clavadetsc...@swisspug.org 
> <mailto:clavadetsc...@swisspug.org> >:
> 
> 
>   Hello Günce
> 
> 
> 
>   From: pgsql-general-ow...@postgresql.org 
> <mailto:pgsql-general-ow...@postgresql.org>  [mailto:pgsql-general-
> ow...@postgresql.org <mailto:pgsql-general-ow...@postgresql.org> ] On Behalf 
> Of Günce Kaya
>

Re: [GENERAL] Fwd: parameter type is unknown error

[Charles Clavadetscher]

Hello Günce

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Günce Kaya
Sent: Montag, 6. März 2017 13:01
To: Charles Clavadetscher <clavadetsc...@swisspug.org>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Fwd: parameter type is unknown error

 

Hi Charles,

 

Thank you for your response. I used pg_typeof() function to show parameters 
type. 

 

select pg_typeof('asdasdasd');

 

result -> "unknown"

 

Well, a string in single quotes is not necessarily a text:

 

db=> select pg_typeof('{"hello": "world"}');

pg_typeof

---

unknown

(1 row)

 

db=> select pg_typeof('{"hello": "world"}'::varchar);

 pg_typeof

---

character varying

(1 row)

 

db=> select pg_typeof('{"hello": "world"}'::text);

pg_typeof

---

text

(1 row)

 

db.archivedb=> select pg_typeof('{"hello": "world"}'::json);

pg_typeof

---

json

(1 row)

 

You probably need to specify that your string is a text.

Bye

Charles

 

but I can use this parameter without custom function as successfully and using 
cast is doesn't work to get result as successful.

 

do you have any advice for that?

 

Regards,

 

Gunce

 

2017-03-06 14:51 GMT+03:00 Charles Clavadetscher <clavadetsc...@swisspug.org 
<mailto:clavadetsc...@swisspug.org> >:

Hello

 

From: pgsql-general-ow...@postgresql.org 
<mailto:pgsql-general-ow...@postgresql.org>  
[mailto:pgsql-general-ow...@postgresql.org 
<mailto:pgsql-general-ow...@postgresql.org> ] On Behalf Of Günce Kaya
Sent: Montag, 6. März 2017 12:36
To: pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org> 
Subject: [GENERAL] Fwd: parameter type is unknown error

 

Hi all,

 

I created a dummy table and related function that include insert script. When I 
execute the function I'm getting error like bellow;

 

ERROR:  function cargo.insertinvoice(integer, integer, integer, unknown, 
unknown, unknown, integer, timestamp with time zone, integer, integer, money, 
timestamp with time zone, timestamp with time zone, timestamp with time zone) 
does not exist

LINE 1: select * from cargo.insertinvoice(1013, 10, 44, 'asda','test...

  ^

HINT:  No function matches the given name and argument types. You might need to 
add explicit type casts.

 

Table's create script as bellow;

 

CREATE TABLE cargo.invoice

(

  id bigserial NOT NULL,

  orderid integer NOT NULL,

  cargoid integer NOT NULL,

  invoiceowner integer NOT NULL,

  invoiceaddress character(250) NOT NULL,

  receiverfname character varying(50) NOT NULL,

  receiverlname character varying(50) NOT NULL,

  receiverphone integer NOT NULL,

  sendingdatetime timestamp without time zone DEFAULT now(),

  distance real NOT NULL,

  weight numeric NOT NULL,

  addedtax numeric NOT NULL DEFAULT 8,

  invoiceamount money DEFAULT 0,

  cargocreateddate date,

  cargoupdateddate timestamp without time zone,

  cargocancelled timestamp without time zone);

 

The function that content insert script to cargo.invoice table is following;

 

create or replace function cargo.insertinvoice (forderid integer, fcargoid 
integer, finvoiceowner integer, finvoiceaddress character, freceiverfname 
character varying, freceiverlname character varying, freceiverphone integer, 
fsendingdatetime timestamp without time zone, fdistance real, fweight numeric,  
finvoiceamount money, fcargocreateddate date, fcargoupdateddate timestamp, 
fcargocancelled timestamp without time zone) returns numeric as $$

 

declare v_id bigint;

 

begin

 

insert into cargo.invoice (orderid, cargoid, invoiceowner, invoiceaddress, 
receiverfname, receiverlname, receiverphone, sendingdatetime, distance, weight, 
addedtax, invoiceamount, cargocreateddate, cargoupdateddate, cargocancelled) 

values(forded, fcargoid, finvoiceowner, finvoiceaddress, 
freceiverfname,freceiverlname,freceiverphone, fsendingdatetime, fdistance, 
fweight, faddedtax, finvoiceamount, fcargocreateddate, fcargoupdateddate, 
fcargocancelled);

 

select max(id) into v_id from cargo.invoice;

 

return v_id;

 

end;

 

$$ language plpgsql;

 

 

So, when I execute the function like;

 

select * from cargo.insertinvoice(1013, 10, 44, 'asda','test1','test2', 12345, 
now(), 1,30, 400, now(), now(), now());

 

I'm getting error as you can see in second paragraph even I use cast for three 
"unknown" parameter in insert script which is in function , I can not overcome 
with this issue so I still get same error.  If I use only following script to 
insert values to cargo.invoice table,

 

insert into cargo.invoice ( orderid, cargoid, invoiceowner, invoiceaddress, 
receiverfname, receiverlname, receiverphone, sendingdatetime, distance, weight, 
addedtax, invoiceamount, cargocreateddate, cargoupdateddate, cargocancelled) 

values( 

Re: [GENERAL] Fwd: parameter type is unknown error

[Charles Clavadetscher]

Hello

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Günce Kaya
Sent: Montag, 6. März 2017 12:36
To: pgsql-general@postgresql.org
Subject: [GENERAL] Fwd: parameter type is unknown error

 

Hi all,

 

I created a dummy table and related function that include insert script. When I 
execute the function I'm getting error like bellow;

 

ERROR:  function cargo.insertinvoice(integer, integer, integer, unknown, 
unknown, unknown, integer, timestamp with time zone, integer, integer, money, 
timestamp with time zone, timestamp with time zone, timestamp with time zone) 
does not exist

LINE 1: select * from cargo.insertinvoice(1013, 10, 44, 'asda','test...

  ^

HINT:  No function matches the given name and argument types. You might need to 
add explicit type casts.

 

Table's create script as bellow;

 

CREATE TABLE cargo.invoice

(

  id bigserial NOT NULL,

  orderid integer NOT NULL,

  cargoid integer NOT NULL,

  invoiceowner integer NOT NULL,

  invoiceaddress character(250) NOT NULL,

  receiverfname character varying(50) NOT NULL,

  receiverlname character varying(50) NOT NULL,

  receiverphone integer NOT NULL,

  sendingdatetime timestamp without time zone DEFAULT now(),

  distance real NOT NULL,

  weight numeric NOT NULL,

  addedtax numeric NOT NULL DEFAULT 8,

  invoiceamount money DEFAULT 0,

  cargocreateddate date,

  cargoupdateddate timestamp without time zone,

  cargocancelled timestamp without time zone);

 

The function that content insert script to cargo.invoice table is following;

 

create or replace function cargo.insertinvoice (forderid integer, fcargoid 
integer, finvoiceowner integer, finvoiceaddress character, freceiverfname 
character varying, freceiverlname character varying, freceiverphone integer, 
fsendingdatetime timestamp without time zone, fdistance real, fweight numeric,  
finvoiceamount money, fcargocreateddate date, fcargoupdateddate timestamp, 
fcargocancelled timestamp without time zone) returns numeric as $$

 

declare v_id bigint;

 

begin

 

insert into cargo.invoice (orderid, cargoid, invoiceowner, invoiceaddress, 
receiverfname, receiverlname, receiverphone, sendingdatetime, distance, weight, 
addedtax, invoiceamount, cargocreateddate, cargoupdateddate, cargocancelled) 

values(forded, fcargoid, finvoiceowner, finvoiceaddress, 
freceiverfname,freceiverlname,freceiverphone, fsendingdatetime, fdistance, 
fweight, faddedtax, finvoiceamount, fcargocreateddate, fcargoupdateddate, 
fcargocancelled);

 

select max(id) into v_id from cargo.invoice;

 

return v_id;

 

end;

 

$$ language plpgsql;

 

 

So, when I execute the function like;

 

select * from cargo.insertinvoice(1013, 10, 44, 'asda','test1','test2', 12345, 
now(), 1,30, 400, now(), now(), now());

 

I'm getting error as you can see in second paragraph even I use cast for three 
"unknown" parameter in insert script which is in function , I can not overcome 
with this issue so I still get same error.  If I use only following script to 
insert values to cargo.invoice table,

 

insert into cargo.invoice ( orderid, cargoid, invoiceowner, invoiceaddress, 
receiverfname, receiverlname, receiverphone, sendingdatetime, distance, weight, 
addedtax, invoiceamount, cargocreateddate, cargoupdateddate, cargocancelled) 

values( 1012, 10, 44, 'asdasdasd','xx', 'xxx', 12345, now(), 
1,30,8,400,now(),now(),now());

 

I can insert data successfully. Do you have any idea about that? 

 

One or more parameter don’t have the expected data type. You should check them 
in order to find out which ones are giving trouble.

In the short you may try to cast your calls to now() to timezone without time 
stamp: now()::timestamp.

 

The function now() returns:

 

db=> select pg_typeof(now());

pg_typeof

--

timestamp with time zone

(1 row)

 

And this is different from without time zone.

 

Hope this helps.

Bye

Charles

 

Any help would be appreciated.

 

Regards,

 

-- 

Gunce Kaya

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

[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

Re: [GENERAL] Listing missing records

[Charles Clavadetscher]

As suggested

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ertan Küçükoglu
> Sent: Sonntag, 19. Februar 2017 11:27
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Listing missing records
> 
> Hello,
> 
> Using PostgreSQL 9.6.1 on a Windows 10 64bit, EnterpriseDB binaries.
> 
> I have following table:
> CREATE TABLE report
> (
> id uuid NOT NULL,
> no smallint NOT NULL,
> serial character varying(15) NOT NULL,
> branchcode character varying(10) NOT NULL,
> date timestamp without time zone NOT NULL,
> recordtime timestamp without time zone DEFAULT date_trunc('second'::text, 
> now()),
> CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date)
> )
> 
> Normally, I should have one record for each "serial, branchcode" set every 
> day. Unfortunately, for some reason
> beyond us, we are not getting these records inserted. I am asked to report 
> missing records in the table so that we
> can provide a list to people who are responsible to enter data.
> 
> Some details about data:
> - serial and branchcode values represents different devices. They are always 
> same within themselves.
> - there may be more than one record in a day for a given serial, branchcode
> 
> What I am looking for is a list of serial, branchcode , date columns just for 
> the missing days.
> 
> Some data from table is as follows:
> '76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04
> 23:21:00','2017-02-13 13:13:58'
> '76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04
> 22:50:00','2017-02-13 13:13:58'
> '76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04
> 23:59:00','2017-02-13 13:13:58'
> '76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI2001','50010','2017-02-04
> 23:58:00','2017-02-13 13:13:58'
> '76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04
> 23:50:00','2017-02-13 13:13:58'
> '9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06
> 23:59:00','2017-02-13 13:13:58'
> '9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI2001','50010','2017-02-06
> 23:58:00','2017-02-13 13:13:58'
> '9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06
> 23:50:00','2017-02-13 13:13:58'
> '909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06
> 23:58:00','2017-02-13 13:13:58'
> 
> When looked in detail you can see that there is no record for date 
> '2017-02-05' above. As a query result I am
> looking for something like below:
> JH20065321, 50010, 2017-02-05, Sunday
> JI2001, 50010, 2017-02-05, Sunday
> JH20064415, 50010, 2017-02-05, Sunday
> JI20049362, 50009, 2017-02-05, Sunday
> JI20049362, 50009, 2017-02-05, Sunday

CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT date_trunc('second'::text, 
now()),
CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date)
);

INSERT INTO report VALUES
('76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04 
23:21:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04 
22:50:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04 
23:59:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI2001','50010','2017-02-04 
23:58:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04 
23:50:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06 
23:59:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI2001','50010','2017-02-06 
23:58:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06 
23:50:00','2017-02-13 13:13:58'),
('909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06 
23:58:00','2017-02-13 13:13:58');

SELECT * FROM (SELECT serial,branchcode,
  generate_series(min(date),max(date),'1 
day'::INTERVAL)::DATE AS date
   FROM report
   GROUP BY serial,branchcode) x
WHERE (x.serial,x.branchcode,x.date) NOT IN
  (SELECT report.serial,report.branchcode,report.date::date
   FROM report
   GROUP BY report.serial,report.branchcode,report.date::date);

   serial   | branchcode |date
++
 JH20065321 | 50010  | 2017-02-05
 JI20049362 | 50009  | 2017-02-05
 JI2001 | 50010  | 2017-02-05
 JH20064415 | 50010  | 2017-02-05
(4 rows)

Regards
Charles

> 
> Thanks & regards,
> Ertan Küçükoğlu
> 
> 
> 
> 
> --
> Sent via pgsql-general mailing list 

Re: [GENERAL] Get tables ending with numbers

[Charles Clavadetscher]

Hello Sathesh

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sathesh S
> Sent: Mittwoch, 15. Februar 2017 07:17
> To: pgsql-general 
> Subject: [GENERAL] Get tables ending with numbers
> 
> Hi All,
> 
> Im trying to get tables ending with numbers (last 8 characters should be 
> numbers).
> 
> For example: I have the tables "test_20160215" and "test_20160131" and 
> "test_1". When i run the below sql im not
> getting any output.
> 
> Select relname from pg_class where relname like '%[0-9]'

You should use an operator for regexp:

CREATE TABLE test_20160215 (id integer);
CREATE TABLE

SELECT relname FROM pg_class WHERE relname ~ '[0-9]';
relname
---
[snip]
 test_20160215
[snip]

Or

SELECT relname FROM pg_class WHERE relname ~ 'test_[0-9]+$';
relname
---
 test_20160215
(1 row)

Regards
Charles

> Can someone please give some idea on this.
> 
> Thanks,
> Sathesh
> 




-- 
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] Special index for "like"-based query

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Job
> Sent: Freitag, 30. Dezember 2016 11:42
> To: Francisco Olarte 
> Cc: David G. Johnston ; 
> pgsql-general@postgresql.org
> Subject: R: [GENERAL] Special index for "like"-based query
> 
> >>And, basically, if you need help with some queries you could try
> >>posting them whole, even redacted, along the table defs, this way
> >>perople can see the problem and not invent one based on a partial
> >>description
> 
> Thank you very much, very kind from you.
> 
> The index applied on the timestamp field is a btree("timestamp")
> 
> The query is:
> 
> select domain, sum(accessi) as c_count from TABLE where action='1' AND 
> profile IN ('PROFILE_CODE') AND
> timestamp::date  BETWEEN '2016-12-27' AND '2016-12-30' AND timestamp::time 
> BETWEEN '00:00:00' AND '23:59:59' GROUP
> BY domain ORDER BY c_count DESC  LIMIT 101
> 
> The table format is:
>   Column   |   Type   |   
> Modifiers
> ---+--+-
> ---
>  id| numeric(1000,1)  | not null default
> function_get_next_sequence('webtraffic_archive_id_seq'::text)
>  timestamp | timestamp with time zone |
>  domain| character varying(255)   |
>  action| character varying(5) |
>  profile   | character varying|
>  accessi   | bigint   |
>  url   | text |
> 
> Indexes:
> "webtraffic_archive_day_2016_04_15_action_wbidx" btree (action)
> "webtraffic_archive_day_2016_04_15_domain_wbidx" btree (domain)
> "webtraffic_archive_day_2016_04_15_profile_wbidx" btree (profile) CLUSTER
> "webtraffic_archive_day_2016_04_15_timestamp_wbidx" btree ("timestamp")

Is the index on timestamp used at all? The index on timestamp is built on 
timestamp, but you query using timestamp::date.
You can check this using EXPLAIN.

Bye
Charles

> 
> Last question: the table is partitioned. I need to manually create index for 
> every sub-tables or there is a way to
> create on every sub-tables once?
> 
> THANK YOU!
> /F
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



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

FW: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

[Charles Clavadetscher]

Forwarding to list.

-Original Message-
From: ajmcello [mailto:ajmcell...@gmail.com] 
Sent: Freitag, 30. Dezember 2016 07:05
To: Charles Clavadetscher <clavadetsc...@swisspug.org>
Subject: Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

There are no connections except one cli when running the query. After that 
finishes then I get connection refused  or cannot connect
to server due to load increasing because of server connections. But I'm more 
interested in tuning the server for better query
response time. Is there anything in the configuration that would help?

Sent from my iPhone

> On Dec 29, 2016, at 7:35 PM, Charles Clavadetscher 
> <clavadetsc...@swisspug.org> wrote:
> 
> Hello
> 
>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org 
>> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of ajmcello
>> Sent: Freitag, 30. Dezember 2016 05:54
>> To: POSTGRES <pgsql-general@postgresql.org>
>> Subject: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]
>> 
>> 
>> I am trying to optimize and tune my server for fastest simple queries with 
>> highest connection to server possible.
>> Basically, a SELECT item from table takes 30 minutes on a machine with SSD 
>> drives. The table has 900K entries and 12
>> columns.  Using that SELECT query, I then have the ability to make about 500 
>> simultaneous connections to the server
>> before errors start to occur. So, I can live with 500, but the slow query 
>> gives me grief.
> 
> From previous posts of other users, I assume that in order to get help you 
> will need to provide some more information. Here the
questions that come to my mind.
> 
> What errors do you get from the server when you reach the 500 connections?
> 
> How long does it take to run the query without heavy load, e.g. just one user 
> connected?
> 
> \timing on
> query
> 
> How does the execution plan look like? There you may see if the index is used 
> at all.
> 
> EXPLAIN ANALYZE query;
> 
>> I have a GIST index for the table. I tried btree, but according to 
>> performance tests, GIST was faster. So I went
>> with GIST.
>> 
>> The system drive and pgsql drive are separate. I can separate them further 
>> if need to be. Total ram is 56GB. I added
>> 32GB of swap.
>> 
>> Is there anything I can do to speed up the SELECT statement?
> 
> Could you provide the statement and the table definition?
> 
>> Here is what I have:
>> 
>> [sysctl.conf]
>> net.ipv4.conf.default.rp_filter=1
>> net.ipv4.conf.all.rp_filter=1
>> net.ipv4.tcp_syncookies=1
>> net.ipv4.ip_forward=1
>> net.ipv6.conf.all.forwarding=1
>> net.ipv4.conf.all.accept_redirects=0
>> net.ipv6.conf.all.accept_redirects=0
>> net.ipv4.conf.all.accept_source_route=0
>> net.ipv6.conf.all.accept_source_route=0
>> net.ipv4.conf.all.log_martians=1
>> kernel.sysrq=0
>> kernel.shmmax=214748399
>> kernel.shmall=209715999
>> #32GBkernel.shmmax=17179869184
>> #32GBkernel.shmall=4194304
>> kernel.shmmni=9
>> kernel.shmmin=1
>> kernel.shmseg=10
>> semmsl, semmns, semopm, semmni kernel.sem=250 32000 100 128
>> fs.file-max=65536
>> kern.maxfiles=5
>> kern.maxfilesperproc=5
>> net.ipv4.ip_local_port_range=1024 65535
>> net.ipv4.tcp_tw_recycle=1
>> net.ipv4.tcp_fin_timeout=10
>> net.ipv4.tcp_tw_reuse=1
>> net.core.rmem_max=16777216
>> net.core.wmem_max=16777216
>> net.ipv4.tcp_max_syn_backlog=4096
>> net.ipv4.tcp_syncookies=1
>> kernel.sched_migration_cost_ns=500
>> kernel.sched_migration_cost_ns=500
>> kernel.sched_autogroup_enabled=0
>> vm.swappiness=10
>> 
>> 
> 
> Here are some helpful informations on the settings below:
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> 
> I am not really the big expert, but 100'000 max_connections and work_mem of 
> 100MB seems to me to be a problem:
> 
> From the link mentioned right above:
> 
> "This size (work_mem) is applied to each and every sort done by each user, 
> and complex queries can use multiple working memory
sort buffers. Set it to 50MB, and have 30 users submitting queries, and you are 
soon using 1.5GB of real memory."
> 
> This is:
> SELECT * FROM pg_size_pretty((50.0*(2^20)*30.0)::BIGINT);
> -[ RECORD 1 ]--+
> pg_size_pretty | 1500 MB
> 
> Applied to your settings:
> 
> SELECT * FROM pg_size_pretty((100.0*(2^20)*10.0)::BIGINT);
> -[ RECORD 1 ]--+
> pg_size_pretty | 9766 GB
> 
> This could explain the err

Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of ajmcello
> Sent: Freitag, 30. Dezember 2016 05:54
> To: POSTGRES 
> Subject: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]
> 
> 
> I am trying to optimize and tune my server for fastest simple queries with 
> highest connection to server possible.
> Basically, a SELECT item from table takes 30 minutes on a machine with SSD 
> drives. The table has 900K entries and 12
> columns.  Using that SELECT query, I then have the ability to make about 500 
> simultaneous connections to the server
> before errors start to occur. So, I can live with 500, but the slow query 
> gives me grief.

>From previous posts of other users, I assume that in order to get help you 
>will need to provide some more information. Here the questions that come to my 
>mind.

What errors do you get from the server when you reach the 500 connections?

How long does it take to run the query without heavy load, e.g. just one user 
connected?

\timing on
query

How does the execution plan look like? There you may see if the index is used 
at all.

EXPLAIN ANALYZE query;

> I have a GIST index for the table. I tried btree, but according to 
> performance tests, GIST was faster. So I went
> with GIST.
> 
> The system drive and pgsql drive are separate. I can separate them further if 
> need to be. Total ram is 56GB. I added
> 32GB of swap.
> 
> Is there anything I can do to speed up the SELECT statement?

Could you provide the statement and the table definition?

> Here is what I have:
> 
> [sysctl.conf]
> net.ipv4.conf.default.rp_filter=1
> net.ipv4.conf.all.rp_filter=1
> net.ipv4.tcp_syncookies=1
> net.ipv4.ip_forward=1
> net.ipv6.conf.all.forwarding=1
> net.ipv4.conf.all.accept_redirects=0
> net.ipv6.conf.all.accept_redirects=0
> net.ipv4.conf.all.accept_source_route=0
> net.ipv6.conf.all.accept_source_route=0
> net.ipv4.conf.all.log_martians=1
> kernel.sysrq=0
> kernel.shmmax=214748399
> kernel.shmall=209715999
> #32GBkernel.shmmax=17179869184
> #32GBkernel.shmall=4194304
> kernel.shmmni=9
> kernel.shmmin=1
> kernel.shmseg=10
> semmsl, semmns, semopm, semmni kernel.sem=250 32000 100 128
> fs.file-max=65536
> kern.maxfiles=5
> kern.maxfilesperproc=5
> net.ipv4.ip_local_port_range=1024 65535
> net.ipv4.tcp_tw_recycle=1
> net.ipv4.tcp_fin_timeout=10
> net.ipv4.tcp_tw_reuse=1
> net.core.rmem_max=16777216
> net.core.wmem_max=16777216
> net.ipv4.tcp_max_syn_backlog=4096
> net.ipv4.tcp_syncookies=1
> kernel.sched_migration_cost_ns=500
> kernel.sched_migration_cost_ns=500
> kernel.sched_autogroup_enabled=0
> vm.swappiness=10
> 
> 

Here are some helpful informations on the settings below:
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

I am not really the big expert, but 100'000 max_connections and work_mem of 
100MB seems to me to be a problem:

From the link mentioned right above:

"This size (work_mem) is applied to each and every sort done by each user, and 
complex queries can use multiple working memory sort buffers. Set it to 50MB, 
and have 30 users submitting queries, and you are soon using 1.5GB of real 
memory."

This is:
SELECT * FROM pg_size_pretty((50.0*(2^20)*30.0)::BIGINT);
-[ RECORD 1 ]--+
pg_size_pretty | 1500 MB

Applied to your settings:

SELECT * FROM pg_size_pretty((100.0*(2^20)*10.0)::BIGINT);
-[ RECORD 1 ]--+
pg_size_pretty | 9766 GB

This could explain the errors you get from the server. You may be trying to use 
much more memory than you have.

Regards
Charles

> [postgresql.conf]
> max_connections = 10
> max_files_per_process = 100
> shared_buffers = 24GB
> max_locks_per_transaction  = 1000
> effective_cache_size = 50GB
> work_mem = 100MB
> maintenance_work_mem = 2GB
> log_min_duration_statement = 1
> checkpoint_completion_target = 0.9
> wal_buffers = 32MB
> default_statistics_target = 100
> listen_addresses = '*'
> port = 5432
> ssl = off
> wal_sync_method = fdatasync
> synchronous_commit = on
> fsync = off
> wal_level = minimal
> #client_min_messages = fatal
> #log_min_messages = fatal
> #log_min_error_statement = fatal
> datestyle = 'iso, mdy'
> debug_pretty_print = off
> debug_print_parse = off
> debug_print_plan = off
> debug_print_rewritten = off
> default_text_search_config = 'pg_catalog.english'
> enable_bitmapscan = on
> enable_hashagg = on
> enable_hashjoin = on
> enable_indexonlyscan = on
> enable_indexscan = on
> enable_material = on
> enable_mergejoin = on
> enable_nestloop = on
> enable_seqscan = on
> enable_sort = on
> enable_tidscan = on
> from_collapse_limit = 8
> geqo = on
> geqo_threshold = 12
> log_checkpoints = off
> 
> log_connections = off
> log_disconnections = off
> log_duration = off
> log_executor_stats = off
> log_hostname = off
> log_parser_stats = off
> log_planner_stats = off
> log_replication_commands = off
> log_statement_stats 

Re: [GENERAL] Book or other resource on Postgres-local code?

[Charles Clavadetscher]

Hi

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guyren Howe
Sent: Donnerstag, 29. Dezember 2016 23:32
To: pgsql-general 
Subject: [GENERAL] Book or other resource on Postgres-local code?

 

As an aside from my last question about my LYDB effort:

 

https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb

 

I would like to find a book or other resource about SQL server-side programming 
(stored procedures etc) best practices in general and for Postgres in 
particular.

 

I found this book quite helpful (I have the first edition, but I assume that 
the second is even better):

 

https://www.packtpub.com/big-data-and-business-intelligence/postgresql-server-programming-second-edition/?utm_source=PoD
 

 _medium=referral_campaign=1783980583

 

Regards

Charles

 

 

Seems like a thing that should exist. I can find a variety of blog posts, 
mostly about Oracle and MS SQL Server, but nothing that appears particularly 
canonical or “classic”.

 

Is there such a resource?

Re: [GENERAL] Delete from table conditionally

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Arup Rakshit
> Sent: Samstag, 17. Dezember 2016 08:39
> To: Pgsql-general 
> Subject: [GENERAL] Delete from table conditionally
> 
> Hi,
> 
> I am using Postgresql 9.5.
> 
> Below is the sample data taken from the mail table with only required columns:
> 
> id | question_id | answer_id | content
> +-+---+--
> 2 |  25 |   270 | Arup
> 3 |  26 |   276 | Kajal
> 4 |  26 |   276 | Arup
> 5 |  27 |   280 | Kajal
> 6 |  25 |   270 | Arup
> 7 |  25 |   270 | Lalu
> 8 |  25 |   270 | Arup
> 9 |  26 |   274 |  Dilip
> 10 |  25 |   270 | doli
> 11 |  25 |   270 | Arup
> (10 rows)
> 
> 
> Say, I know the id 6 where content is "Arup". Now from this row, I want to 
> delete all next rows where the content is
> "Arup". How should I achieve this?

Assuming that "next" means id > 6:

DELETE FROM 
WHERE id > 6
AND content = 'Arup';

Regards
Charles

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



-- 
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] Recursive row level security policy

[Charles Clavadetscher]

Hello again

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Freitag, 16. Dezember 2016 07:41
> To: 'Simon Charette' <charett...@gmail.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Recursive row level security policy
> 
> Hello Simon
> 
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Simon
> > Charette
> > Sent: Freitag, 16. Dezember 2016 07:02
> > To: Charles Clavadetscher <clavadetsc...@swisspug.org>
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Recursive row level security policy
> >
> > Hello Charles,
> >
> > Unfortunately this will only return accounts matching the current_user's 
> > name.
> >
> > I would expect "SET ROLE foo; SELECT name FROM accounts" to return
> > "foo" and "bar" and not only "foo" like your proposed solution would do.
> 
> True. I did oversee the real target.
> 
> The problem is that the policy for select on the table will be cheked each 
> time a select is performed. So having a
> select in the using condition will check the policy again, and so on.
> 
> I am not sure how to solve this with policies and I assume that somebody else 
> may come up with an idea. One thing I
> can think of is to check the condition in a security definer function where 
> you temporarily disable row level
> security. But this is quite a nasty thing to do...

Forget this. It would not work anyway.

> A workaround would be the "old way" using views:
> 
> CREATE VIEW public.v_accounts AS
>   SELECT * FROM accounts
>   WHERE owner_id = (SELECT owner_id FROM accounts WHERE name = CURRENT_USER);
> 
> REVOKE SELECT ON accounts FROM public;
> GRANT SELECT ON v_accounts TO public;
> 
> charles@charles=# set role foo;
> SET
> 
> charles@charles=> SELECT * FROM accounts;
> ERROR:  permission denied for relation accounts
> 
> charles@charles=> select * from v_accounts ;  id | name | owner_id
> +--+--
>   1 | foo  |1
>   2 | bar  |1
> (2 rows)
> 
> Instead of granting select on the table you only grant it on the view.
> 
> Hope this helps.
> Bye
> Charles
> 
> >
> > Simon
> >
> > 2016-12-16 0:57 GMT-05:00 Charles Clavadetscher 
> > <clavadetsc...@swisspug.org>:
> > > Hello
> > >
> > >> -Original Message-
> > >> From: pgsql-general-ow...@postgresql.org
> > >> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Simon
> > >> Charette
> > >> Sent: Freitag, 16. Dezember 2016 06:15
> > >> To: pgsql-general@postgresql.org
> > >> Subject: [GENERAL] Recursive row level security policy
> > >>
> > >> Hello there,
> > >>
> > >> I'm not sure I'm posting to the appropriate mailing list so don't 
> > >> hesitate to redirect me to the appropriate
> one.
> > >>
> > >> I've been trying to setup a policy that allows "accounts" table
> > >> rows to only be seen by their owner by using the current_user to compare 
> > >> them by name.
> > >>
> > >> Unfortunately it looks like I'm either missing something or there's
> > >> a limitation in the current row level security implementation that 
> > >> prevents me from doing this.
> > >>
> > >> Here's the actual SQL to reproduce the issue:
> > >>
> > >> CREATE TABLE "accounts" (
> > >> "id" integer NOT NULL PRIMARY KEY,
> > >> "name" varchar(50) NOT NULL UNIQUE,
> > >> "owner_id" integer NOT NULL
> > >> );
> > >>
> > >> INSERT INTO accounts(id, name, owner_id)
> > >> VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);
> > >>
> > >> GRANT SELECT ON accounts TO PUBLIC;
> > >>
> > >> ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
> > >>
> > >> CREATE POLICY account_ownership ON accounts FOR SELECT
> > >> USING (owner_id = (SELECT id FROM accounts WHERE name =
> > >> current_user));
> > >
> > > I think that should be:
> > >
> > > CREATE POLICY account_ownership ON accounts FOR SELECT
> > > USING (name = current_user);
> > >
> > > Regards
> > > C

Re: [GENERAL] Recursive row level security policy

[Charles Clavadetscher]

Hello Simon

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Simon Charette
> Sent: Freitag, 16. Dezember 2016 07:02
> To: Charles Clavadetscher <clavadetsc...@swisspug.org>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Recursive row level security policy
> 
> Hello Charles,
> 
> Unfortunately this will only return accounts matching the current_user's name.
> 
> I would expect "SET ROLE foo; SELECT name FROM accounts" to return "foo" and 
> "bar" and not only "foo" like your
> proposed solution would do.

True. I did oversee the real target.

The problem is that the policy for select on the table will be cheked each time 
a select is performed. So having a select in the using condition will check the 
policy again, and so on.

I am not sure how to solve this with policies and I assume that somebody else 
may come up with an idea. One thing I can think of is to check the condition in 
a security definer function where you temporarily disable row level security. 
But this is quite a nasty thing to do...

A workaround would be the "old way" using views:

CREATE VIEW public.v_accounts AS
  SELECT * FROM accounts
  WHERE owner_id = (SELECT owner_id FROM accounts WHERE name = CURRENT_USER);

REVOKE SELECT ON accounts FROM public;
GRANT SELECT ON v_accounts TO public;

charles@charles=# set role foo;
SET

charles@charles=> SELECT * FROM accounts;
ERROR:  permission denied for relation accounts

charles@charles=> select * from v_accounts ;
 id | name | owner_id
+--+--
  1 | foo  |1
  2 | bar  |1
(2 rows)

Instead of granting select on the table you only grant it on the view.

Hope this helps.
Bye
Charles

> 
> Simon
> 
> 2016-12-16 0:57 GMT-05:00 Charles Clavadetscher <clavadetsc...@swisspug.org>:
> > Hello
> >
> >> -Original Message-
> >> From: pgsql-general-ow...@postgresql.org
> >> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Simon
> >> Charette
> >> Sent: Freitag, 16. Dezember 2016 06:15
> >> To: pgsql-general@postgresql.org
> >> Subject: [GENERAL] Recursive row level security policy
> >>
> >> Hello there,
> >>
> >> I'm not sure I'm posting to the appropriate mailing list so don't hesitate 
> >> to redirect me to the appropriate one.
> >>
> >> I've been trying to setup a policy that allows "accounts" table rows
> >> to only be seen by their owner by using the current_user to compare them 
> >> by name.
> >>
> >> Unfortunately it looks like I'm either missing something or there's a
> >> limitation in the current row level security implementation that prevents 
> >> me from doing this.
> >>
> >> Here's the actual SQL to reproduce the issue:
> >>
> >> CREATE TABLE "accounts" (
> >> "id" integer NOT NULL PRIMARY KEY,
> >> "name" varchar(50) NOT NULL UNIQUE,
> >> "owner_id" integer NOT NULL
> >> );
> >>
> >> INSERT INTO accounts(id, name, owner_id)
> >> VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);
> >>
> >> GRANT SELECT ON accounts TO PUBLIC;
> >>
> >> ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
> >>
> >> CREATE POLICY account_ownership ON accounts FOR SELECT
> >> USING (owner_id = (SELECT id FROM accounts WHERE name =
> >> current_user));
> >
> > I think that should be:
> >
> > CREATE POLICY account_ownership ON accounts FOR SELECT
> > USING (name = current_user);
> >
> > Regards
> > Charles
> >
> >>
> >> CREATE ROLE foo;
> >> SET ROLE foo;
> >> SELECT * FROM accounts;
> >> -- ERROR:  infinite recursion detected in policy for relation "accounts"
> >>
> >> Is there any way to alter the "account_ownership" policy's USING
> >> clause to avoid this infinite recursion or a way to model my schema to 
> >> prevent this from happening?
> >>
> >> Thank you for your time,
> >> Simon
> >>
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> >> changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] Recursive row level security policy

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Simon Charette
> Sent: Freitag, 16. Dezember 2016 06:15
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Recursive row level security policy
> 
> Hello there,
> 
> I'm not sure I'm posting to the appropriate mailing list so don't hesitate to 
> redirect me to the appropriate one.
> 
> I've been trying to setup a policy that allows "accounts" table rows to only 
> be seen by their owner by using the
> current_user to compare them by name.
> 
> Unfortunately it looks like I'm either missing something or there's a 
> limitation in the current row level security
> implementation that prevents me from doing this.
> 
> Here's the actual SQL to reproduce the issue:
> 
> CREATE TABLE "accounts" (
> "id" integer NOT NULL PRIMARY KEY,
> "name" varchar(50) NOT NULL UNIQUE,
> "owner_id" integer NOT NULL
> );
> 
> INSERT INTO accounts(id, name, owner_id)
> VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);
> 
> GRANT SELECT ON accounts TO PUBLIC;
> 
> ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
> 
> CREATE POLICY account_ownership ON accounts FOR SELECT
> USING (owner_id = (SELECT id FROM accounts WHERE name = current_user));

I think that should be:

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (name = current_user);

Regards
Charles

> 
> CREATE ROLE foo;
> SET ROLE foo;
> SELECT * FROM accounts;
> -- ERROR:  infinite recursion detected in policy for relation "accounts"
> 
> Is there any way to alter the "account_ownership" policy's USING clause to 
> avoid this infinite recursion or a way to
> model my schema to prevent this from happening?
> 
> Thank you for your time,
> Simon
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] Importing SQLite database

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver
> Sent: Donnerstag, 8. Dezember 2016 16:09
> To: Igor Korot 
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Importing SQLite database
> 
> On 12/08/2016 06:54 AM, Igor Korot wrote:
> > Adrian,
> >
> > On Thu, Dec 8, 2016 at 9:47 AM, Adrian Klaver  
> > wrote:
> >> On 12/08/2016 04:54 AM, Igor Korot wrote:
> >>>
> >>> Hi, ALL,
> >>> I have a text file which I got from exporting the SQLite database.
> >>>
> >>> The file contains an SQL statement which will generate the database.
> >>
> >>
> >> Is there a CREATE DATABASE statement in the file you are referencing?
> >
> > Well there is no CREATE DATABASE() in the SQLite.
> > But I created a database by hand, so no issue here.
> >
> >>
> >> Otherwise you will have to create the database first and then load
> >> the file into it.
> >>
> >>>
> >>> Excerpt from this file:
> >>>
> >>> [code]
> >>> CREATE TABLE leagues( id INTEGER AUTOINCREMENT PRIMARY KEY, name
> >>> VARCHAR(100),balance DOUBLE(10,2)); CREATE TABLE players(playerid
> >>> INTEGER AUTOINCREMENT PRIMARY KEY, player_name VARCHAR(60),
> >>> player_position CHAR(1)); CREATE TABLE player_draft(id INTEGER,
> >>> playerid INTEGER, drafted_position CHAR(1), FOREIGN KEY id REFERENCE
> >>> leagues(id), FOREIGN KEY playerid REFERENCE players(playerid));
> >>> INSERT INTO leagues VALUES(1, 'Test1', 260.00); INSERT INTO leagues
> >>> VALUES(2, 'Test2', 260.00); INSERT INTO players VALUES(1, 'Player1',
> >>> 'D'); INSERT INTO players VALUES(2, ''Player2', 'F'); [/code]
> >>>
> >>> My question would be:
> >>> Is there a command in Postgres which will open this file and execute
> >>> all those commands one-by-one in a transaction?
> >>
> >>
> >> Yes there is assuming the [code][/code] tags are for the email only.
> >
> > Yes, "code" tags are for e-mail only.
> >
> >>
> >> The issue is that Postgres will not understand AUTOINCREMENT. In Postgres 
> >> to
> >> get the same behavior you would do:
> >>
> >> id SERIAL PRIMARY KEY
> >
> > I'm not very familiar with Postgres, so let me ask you - is
> > autoincrementing behavior set
> > by default for the primary key?
> > Like if I want to insert the record in the table and if I omit this
> > column it will get the last inserted
> > value in this table plus 1.
> 
> No that is a Sqlite thing:
> http://sqlite.org/autoinc.html
> 
> 
> If you want to replicate in Postgres you will need to use the SERIAL type:
> 
> https://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL
> 
> along with PRIMARY KEY so:
> 
> id SERIAL PRIMARY KEY
> 
> This sets up a SEQUENCE:
> 
> https://www.postgresql.org/docs/9.5/static/sql-createsequence.html
> 
> for the id column, which supplies an incrementing, but not necessarily
> gap free sequence of numbers.

Adding to that. The sequence is unaware of the value that already are available 
in the column. Your insert statements will create rows without changing the 
sequence. That means that after finishing the import you will need to set the 
value of the sequence to the maximum value available in the column.

Here an example:

db=> create table test (id serial primary key, txt text);
CREATE TABLE
db=> \d test
 Table "public.test"
 Column |  Type   | Modifiers
+-+---
 id | integer | not null default nextval('test_id_seq'::regclass)
 txt| text|
Indexes:
"test_pkey" PRIMARY KEY, btree (id)

db=> insert into test values (1,'asdf'),(2,'fdgd'),(3,'werwe');
INSERT 0 3

db=> select * from test_id_seq;
 sequence_name | last_value | start_value | increment_by |  max_value  
| min_value | cache_value | log_cnt | is_cycled | is_called
---++-+--+-+---+-+-+---+---
 test_id_seq   |  1 |   1 |1 | 9223372036854775807 
| 1 |   1 |   0 | f | f
(1 row)

Since the value of the sequence still is 1 you may get into trouble:

db=> insert into test (txt) values ('hallo');
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.

So you set the value of the sequence:

db.archivedb=> select setval('test_id_seq'::regclass,(select max(id) from 
test));
 setval

  3
(1 row)

And then everything works as expected.

db=> insert into test (txt) values ('hallo');
INSERT 0 1
kofadmin@kofdb.archivedb=> select * from test;
 id |  txt
+---
  1 | asdf
  2 | fdgd
  3 | werwe
  4 | hallo
(4 rows)

Hope this helps.
Bye
Charles

> 
> >
> >>
> >> If you clean up the file you can do, assuming you created a database called
> >> some_database:
> >>
> >> psql -d some_database -U some_user -f your_file
> 

Re: [GENERAL] max_connections limit violation not showing in pg_stat_activity

[Charles Clavadetscher]

Hello Kevin

Getting back at this.

> -Original Message-
> From: Kevin Grittner [mailto:kgri...@gmail.com]
> Sent: Mittwoch, 23. November 2016 17:04
> To: Charles Clavadetscher <clavadetsc...@swisspug.org>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] max_connections limit violation not showing in 
> pg_stat_activity
> 
> On Wed, Nov 23, 2016 at 4:43 AM, Charles Clavadetscher 
> <clavadetsc...@swisspug.org> wrote:
> > From: Kevin Grittner [mailto:kgri...@gmail.com]
> 
> >> Is it possible to upgrade?  You are missing over a year's worth of
> >> fixes for serious bugs and security vulnerabilities.
> >
> > Yes. Actually it is foreseen to move to 9.6 (and RedHat 7.x).
> >
> >> https://www.postgresql.org/support/versioning/
> 
> An upgrade from 9.3.x to 9.6.x is a major release upgrade, which gets you new 
> features and usually gets you
> improvements in performance and scalability.  The 9.3 major release will be 
> supported for almost 2 more years, so I
> wasn't so concerned about that as being on 9.3.10 when the latest bug fix 
> version of 9.3 is 9.3.15.  To avoid
> hitting bugs that others have already hit and reported, with fixes published, 
> it is wise to try to upgrade to the
> latest minor release fairly quickly.  If the minor release fixes a serious 
> security vulnerability, I think it is a
> good idea to update within a day or two of release.
> 
> > I will have to check with our hosting people how many cores we have or
> > can have on the new environment.  I have seen that there is pgBouncer
> > and pgPool. Would you recommend one of those?
> 
> pgBouncer is more lightweight, so if you don't need any of the features 
> present only pgPool, I would go with
> pgBouncer.  Depending on your application software environment, it may be 
> even better to use a pool built into the
> application development framework.  There are several very good pools 
> available to Java environments.  I can
> personally attest to the quality of Apache dbcp, but others have mentioned 
> that they like other Java connection
> pools even better.
> You might want to search the archives, or maybe someone will mention the 
> others again on this thread.

Finally I set up pgbouncer and a simple first test with a somewhat heavy load 
(1000 users in 5 minutes corresponding to a total amount of 12000 http requests 
and 5000 DB requests) shows an incredible improvement in performance. Without 
the pooler roughly a fifth of the calls died in a timeout. The remaining show 
an average response time of more than 8 seconds. With the pooler all requests 
went through without any error whatsoever and the mean response time dropped to 
23 ms (the maximum being at 193 ms). At first I thought that I had some error 
in my simulation somewhere, but all 5000 DB requests have been correctly 
performed as I could check in a log table that I prepared for that specific 
purpose. There is definetely not doubt about the beneficial effect of the 
connection pooler.

Now I will take some time to better understand the configuration settings.

Maybe a question on that. I have following configuration entries:

pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 60
min_pool_size = 5
max_db_connections = 60
max_user_connections = 60

The pooler only serves a single application and only has a single connection 
string with the same user for all calls (that being the application user 
defined for that specific web application). Our current DB has a 
max_connections of 100. I assumed that max_client_conn should match this entry. 
With max_db_connections and max_user_connections I try to make sure that calls 
from this  application won't take up all resources (as it happened without the 
pooler). This seems to work correctly. During the simulation other applications 
that also require the database but don't use the pooler, work normally, i.e. 
there are no noticeable effects.

If you have some suggestion or see potential problems in these settings, that I 
am not aware of so far, feel free to point me to them.

Thank you very much again for the excellent tips.
Bye
Charles

> >>> SELECT count(*) FROM pg_stat_activity; watch 1;
> >>
> >> At the times when the resources are overloaded by more connections
> >> than the resources can efficiently service -- well that's precisely
> >> the time that a sleeping "monitoring" process is least likely to be
> >> given a time slice to run. If you can manage to get pgbadger to run
> >> on your environment, and you turn on logging of connections and
> >> disconnections, you will be able to get far more accurate
> >> information.
> >
> > Yes, it sounds reasonable. I a

Re: [GENERAL] Who dropped a role?

[Charles Clavadetscher]

Hello

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Durumdara
Sent: Donnerstag, 8. Dezember 2016 10:13
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Who dropped a role?

 

Hello!

 

Sorry, meanwhile I found it as Group Role.

I never used this kind of role. How to restore it to normal login role?

 

ALTER ROLE ... LOGIN;

 

You probably need to reassign the password (I don’t remember right now if the 
pw is dropped when changing form LOGIN to NOLOGIN).

 

\password ...

 

Thanks for it!

 

dd

 

2016-12-08 9:53 GMT+01:00 Durumdara  >:

Dear PG Masters!

 

In a server today I didn't find an important role.

I don't know what my colleagues did with it, but it seems to be lost.

Do you know a trick, how get info about it?

 

1.) Who dropped it?

2.) If not dropped what happened with it?

 

Does role dropping logged somewhere?

 

Thanks for your help!

 

dd

 

Re: [GENERAL] When to use COMMENT vs --

[Charles Clavadetscher]

Hi Rich

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Travers
> Sent: Mittwoch, 7. Dezember 2016 17:12
> To: Postgres General 
> Subject: Re: [GENERAL] When to use COMMENT vs --
> 
> On Dec 7, 2016 5:07 PM, "Karsten Hilbert"   > wrote:
> >
> > On Wed, Dec 07, 2016 at 07:57:54AM -0800, Rich Shepard wrote:
> >
> > >   I have used '-- ' to enter comments about tables or columns and am
> > > curious about the value of storing comments in tables using the COMMENT 
> > > key word.
> > > When is the latter more appropriate than the former?
> >
> > "--" only means "comment" to SQL code (such as in scripts).
> > PostgreSQL itself simply ignores it.
> >
> > OTOH, using "comment on ... is ..." tells PostgreSQL to _store_ a
> > comment on a database object for later retrieval.
> >
> 
> This also means that tools like pg_autodoc can include it as part of the 
> generated documentation.

All of the relevant differences have been mentioned by previous posters. IMHO 
the fact mentioned by Chris Travers that comments on objects included in the 
database can be used by tools to generate the documentation is probably the 
most important (besides their being persisted). As a matter of fact we have 
integrated this feature to extract comments to generate the DB documentation in 
our internal MediaWiki based wiki.

If you are interested in more details on that, including additional reasons why 
it is a good idea to use "comments on" instead of comments in the source code, 
you may have a look at this presentation:

http://www.schmiedewerkstatt.ch/documents/04-publications/integrating_postgresql_documentation_in_3rd_party_applications_handout_pdfa.pdf

Bye
Charles

> > Karsten
> > --
> > GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> > 
> > E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org
> >  ) To make changes to your 
> > subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> 




-- 
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] Extensions and privileges in public schema

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Paul Ramsey
> Sent: Sonntag, 4. Dezember 2016 22:24
> To: Lee Hachadoorian 
> Cc: pgsql-general 
> Subject: Re: [GENERAL] Extensions and privileges in public schema
> 
> When you create the student user, remove their create privs in public.
> Then create a scratch schema and grant them privs there.
> Finally, alter the student user so that the scratch schema appears FIRST in 
> their search path. This will cause
> unqualified CREATE statements to create in the scratch schema.
> For full separation, give each student their own login and set the search 
> path to
> 
> "$user", public
> 
> That way each student gets their own private scratch area, and it is used by 
> default for their creates.
> 
> P
> 
> 
> 
> On Sun, Dec 4, 2016 at 1:10 PM, Lee Hachadoorian 
> 
> > wrote:
> 
> 
>   This question is specifically motivated by my use of the PostGIS 
> extension, but since other extensions create
> functions and other supporting objects in public schema, I believe it is more 
> general.
> 
>   I'm teaching a university-level class using PostGIS. I have created a 
> scratch schema for students to create
> objects in. At the end of the term I can drop scratch and start fresh the 
> following term.
> 
>   Students of course can also create objects in public schema, and often 
> do unintentionally because the forget
> to schema qualify their CREATE TABLE statements. This complicates things 
> because I can't drop public schema without
> dropping various PostGIS (and other) tables and functions. Additionally, 
> while I doubt the students would do
> something like drop a public function or supporting table (like 
> spatial_ref_sys), it nonetheless seems like a poor
> idea for these database objects to be vulnerable.

You could 

REVOKE CREATE ON SCHEMA public FROM public;

So your students would not be able to create objects in the public schema.

Bye
Charles

> 
>   What is considered best practices in this case? Should PostGIS 
> extension be kept in its own schema (as was
> suggested when I asked about this on GIS.SE  )? If I do so, 
> can I treat public schema the way I have
> been using scratch schema, i.e. could I drop and recreate clean public schema 
> at end of term? Should I leave
> extensions in public but limit rights of public role in that schema (so that 
> they don't unintentionally create
> tables there, or accidentally delete other objects)? Or do Postgres DBA's 
> just not worry about the objects in public
> schema, and rely upon applications and login roles to interact with the 
> database intelligently?
> 
>   To be clear, primary goal is to keep student created objects in one 
> schema which can be dropped at the end of
> the term. But the question of preventing accidental creation/deletion of 
> objects in public schema is possibly
> related, and the overall database organization might address both concerns.
> 
>   Best,
>   --Lee
> 
> 
> 
>   --
> 
>   Lee Hachadoorian
>   Assistant Professor of Instruction, Geography and Urban Studies
>   Assistant Director, Professional Science Master's in GIS
>   Temple University
> 




-- 
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] max_connections limit violation not showing in pg_stat_activity

[Charles Clavadetscher]

Hello Kevin

Thank you very much for your input. I appreciate it very much.

> -Original Message-
> From: Kevin Grittner [mailto:kgri...@gmail.com]
> Sent: Dienstag, 22. November 2016 22:37
> To: Charles Clavadetscher <clavadetsc...@swisspug.org>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] max_connections limit violation not showing in 
> pg_stat_activity
> 
> On Tue, Nov 22, 2016 at 12:48 PM, Charles Clavadetscher 
> <clavadetsc...@swisspug.org> wrote:
> 
> > We are using PostgreSQL 9.3.10 on RedHat (probably 6.x).
> 
> Is it possible to upgrade?  You are missing over a year's worth of fixes for 
> serious bugs and security
> vulnerabilities.

Yes. Actually it is foreseen to move to 9.6 (and RedHat 7.x).

> https://www.postgresql.org/support/versioning/
> 
> > Among other thing the database is the backend for a web application
> > that expects a load of a some hundred users at a time (those are
> > participans to online surveys that we use for computing economic
> > indicators and access the system every month). The whole amount of
> > people expected is above 5000, but we don't expect a too high
> > concurrent access to the database. As mentioned a few hundreds at the 
> > beginning of the surveys.
> >
> > To be sure that we won't have problems with the peak times we created
> > a load test using gatling that ramps up to 1000 users in 5 minutes in
> > bunches of 10. At the beginning we had problems with the web server
> > response that we were able to correct. Now we face problem with the
> > max_connections limit of PostgreSQL. Currently it is set to the
> > default of 100. We are going to look into it and either increase that
> > limit or consider connections pooling.
> 
> On a web site with about 3000 active users, I found (through adjusting the 
> connection pool size on the production
> database and monitoring performance) that we got best performance with a pool 
> of about 40 connections.  This was on
> a machine with 16 cores (never count HT "threads" as cores), 512GB RAM, and a 
> RAID with 40 drives of spinning rust.

OK. I will have to check with our hosting people how many cores we have or can 
have on the new environment.
I have seen that there is pgBouncer and pgPool. Would you recommend one of 
those? AFAICS both are installed on the client side, so that we should be able 
to use them.

> http://tbeitr.blogspot.com/2015/11/for-better-service-please-take-number.html
> 
> > What bothers me however is that running a query on pg_stat_activity
> > with a watch of 1 seconds never shows any value higher than 37 of
> > concurrent active connections.
> >
> > SELECT count(*) FROM pg_stat_activity; watch 1;
> 
> At the times when the resources are overloaded by more connections than the 
> resources can efficiently service --
> well that's precisely the time that a sleeping "monitoring" process is least 
> likely to be given a time slice to run.
> If you can manage to get pgbadger to run on your environment, and you turn on 
> logging of connections and
> disconnections, you will be able to get far more accurate information.

Yes, it sounds reasonable. I assumed that this kind of measurements have a 
higher priority or reserved slots for them. In those occasions is when they are 
most needed.

And thank you for the hint to pgbadger. I will take a look into it, but an 
installation on the server completely depends on our hosting service people. I 
am not sure this is feasible, but I can imagine an alternative scenario, using 
a local installation for tuning and then pass the parameters to the hosters for 
implementation. Might help.

Regards
Charles

> 
> > Increasing max_connections has repercussions on the configuration of
> > work_mem (if I remember well)
> 
> Each connection can allocate one work_mem allocation per node which requires 
> a sort, hash, CTE, etc.
> 
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



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

[GENERAL] max_connections limit violation not showing in pg_stat_activity

[Charles Clavadetscher]

Hello

We are using PostgreSQL 9.3.10 on RedHat (probably 6.x).

The database is hosted by an internal service provider and we have
superuser access to it over a PG client, e.g. psql, but not to the OS.
For that reason we only have access to the log files indirectly using
some of the built in system functions like pg_ls_dir, etc.

Among other thing the database is the backend for a web application that
expects a load of a some hundred users at a time (those are participans
to online surveys that we use for computing economic indicators and
access the system every month). The whole amount of people expected is
above 5000, but we don't expect a too high concurrent access to the
database. As mentioned a few hundreds at the beginning of the surveys.

To be sure that we won't have problems with the peak times we created a
load test using gatling that ramps up to 1000 users in 5 minutes in
bunches of 10. At the beginning we had problems with the web server
response that we were able to correct. Now we face problem with the
max_connections limit of PostgreSQL. Currently it is set to the default
of 100. We are going to look into it and either increase that limit or
consider connections pooling.

What bothers me however is that running a query on pg_stat_activity with
a watch of 1 seconds never shows any value higher than 37 of concurrent
active connections.

SELECT count(*) FROM pg_stat_activity; watch 1;

Due to that fact it took us quite a time to figure out that the
bottleneck had become the database. We discovered it after looking into
the log files (as mentioned above this is not very straightforward, in
particular because the logs tend to become quite huge).

I assume that the peaks of requests violating the limit happen between
two calls of the query. Is there a better way to keep track of this kind
of problems? I felt a bit weird not to be able to discover the issue sooner.

And what would be a reasonable strategy to deal with the problem at
hand? Increasing max_connections has repercussions on the configuration
of work_mem (if I remember well) or on the other hand on the amount of
physical memory that must be available on the system.

On Thursday we are going to have a meeting with our DB hosting provider
to discuss which improvement need to be made to meet the requirements of
our applications (the web application mentioned is not the only one
using the database, but is the only one where we expect such peaks).

So I'd be very grateful for advice on this subject.

Thank you.
Regards
Charles

-- 
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


-- 
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] Request to share information regarding deadlock in postgresql-9.3.6

[Charles Clavadetscher]

Rethinking that

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Donnerstag, 17. November 2016 09:12
> To: 'Yogesh Sharma' <yogesh1.sha...@nectechnologies.in>; 'John R Pierce' 
> <pie...@hogranch.com>; pgsql-
> gene...@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding deadlock in 
> postgresql-9.3.6
> 
> Hello
> 
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Yogesh Sharma
> > Sent: Donnerstag, 17. November 2016 08:31
> > To: John R Pierce <pie...@hogranch.com>; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Request to share information regarding deadlock
> > in postgresql-9.3.6
> >
> > Dear John,
> >
> > Thanks for sharing solution approaches.
> >
> > >do each reindex as a seperate transaction so only one table gets locked at 
> > >a time.
> > Transaction is already in separate like REINDEX TABLE table1, REINDEX TABLE 
> >  table2 etc..
> > But this problem is occurred.
> >
> > One more question regarding below.
> > alter table table1 add constraint fk_key foreign key (id, roll_number)
> > references table2 (id, roll_number) on delete restrict on update restrict; 
> > It shows below error:
> > ERROR:  there is no unique constraint matching given keys for referenced 
> > table "table2"

At second thought I think that your example does not really make sense. You 
probably want something like this:

create table table1
(
   id char(6) not null,
   roll_number varchar(20) not null,
   primary key (id, roll_number)
);

create table table2
(
   id char(6) not null,
   roll_number varchar(20) not null,
   account_id varchar(20) not null default '-',
   primary key (id, roll_number, account_id),
);

alter table table2 add constraint fk_key foreign key (id, roll_number) 
references table1 (id, roll_number) on delete restrict on update restrict;

It would to know what you want to eventually achieve.

Bye
Charles

> As mentioned upthread, the foreign key must match the primary key (more 
> precisely a unique key) of the table it
> references.
> Now the primary key of table2 is (id, roll_number, account_id) but you are 
> trying to reference only a part of it.
> 
> > references table2 (id, roll_number)
>  
> 
> You can add a unique contraint in table2:
> 
> create table table2
> (
>id char(6) not null,
>roll_number varchar(20) not null,
>account_id varchar(20) not null default '-',
>primary key (id, roll_number, account_id),
>unique (id, roll_number)
> );
> 
> create table table1
> (
>id char(6) not null,
>roll_number varchar(20) not null,
>primary key (id, roll_number)
> );
> 
> alter table table1 add constraint fk_key foreign key (id, roll_number) 
> references table2 (id, roll_number) on delete
> restrict on update restrict;
> 
> db.localhost=> \d table1
>   Table "public.table1"
>Column| Type  | Modifiers
> -+---+---
>  id  | character(6)  | not null
>  roll_number | character varying(20) | not null
> Indexes:
> "table1_pkey" PRIMARY KEY, btree (id, roll_number) Foreign-key 
> constraints:
> "fk_key" FOREIGN KEY (id, roll_number) REFERENCES table2(id, roll_number) 
> ON UPDATE RESTRICT ON DELETE RESTRICT
> 
> Regards
> Charles
> 
> > Table1 contains below structure:
> > create table table1
> > (
> >idchar(6) not null,
> >roll_number   varchar(20) not null,
> > -
> > primary key (id, roll_number)
> >
> > Table2 contains below structure:
> >
> > create table table2
> > (
> >idchar(6) not null,
> >roll_number   varchar(20) not null,
> > account_id  varchar(20) not null default '-',
> > -
> > primary key (id, roll_number, account_id)
> >
> > How to resolve this issue?
> >
> > Regards,
> > Yogesh
> >
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
> > Sent: Thursday, November 17, 2016 12:04 PM
> > To: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Request to share information regarding deadlock
&g

Re: [GENERAL] Request to share information regarding deadlock in postgresql-9.3.6

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Yogesh Sharma
> Sent: Donnerstag, 17. November 2016 08:31
> To: John R Pierce ; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding deadlock in 
> postgresql-9.3.6
> 
> Dear John,
> 
> Thanks for sharing solution approaches.
> 
> >do each reindex as a seperate transaction so only one table gets locked at a 
> >time.
> Transaction is already in separate like REINDEX TABLE table1, REINDEX TABLE  
> table2 etc..
> But this problem is occurred.
> 
> One more question regarding below.
> alter table table1 add constraint fk_key foreign key (id, roll_number) 
> references table2 (id, roll_number) on delete
> restrict on update restrict; It shows below error:
> ERROR:  there is no unique constraint matching given keys for referenced 
> table "table2"

As mentioned upthread, the foreign key must match the primary key (more 
precisely a unique key) of the table it references.
Now the primary key of table2 is (id, roll_number, account_id) but you are 
trying to reference only a part of it.

> references table2 (id, roll_number)
 

You can add a unique contraint in table2:

create table table2
(
   id char(6) not null,
   roll_number varchar(20) not null,
   account_id varchar(20) not null default '-',
   primary key (id, roll_number, account_id),
   unique (id, roll_number)
);

create table table1
(
   id char(6) not null,
   roll_number varchar(20) not null,
   primary key (id, roll_number)
);

alter table table1 add constraint fk_key foreign key (id, roll_number) 
references table2 (id, roll_number) on delete restrict on update restrict;

db.localhost=> \d table1
  Table "public.table1"
   Column| Type  | Modifiers
-+---+---
 id  | character(6)  | not null
 roll_number | character varying(20) | not null
Indexes:
"table1_pkey" PRIMARY KEY, btree (id, roll_number)
Foreign-key constraints:
"fk_key" FOREIGN KEY (id, roll_number) REFERENCES table2(id, roll_number) 
ON UPDATE RESTRICT ON DELETE RESTRICT

Regards
Charles

> Table1 contains below structure:
> create table table1
> (
>idchar(6) not null,
>roll_number   varchar(20) not null,
> -
> primary key (id, roll_number)
> 
> Table2 contains below structure:
> 
> create table table2
> (
>idchar(6) not null,
>roll_number   varchar(20) not null,
> account_id  varchar(20) not null default '-',
> -
> primary key (id, roll_number, account_id)
> 
> How to resolve this issue?
> 
> Regards,
> Yogesh
> 
> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
> Sent: Thursday, November 17, 2016 12:04 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding deadlock in 
> postgresql-9.3.6
> 
> On 11/16/2016 6:22 PM, Yogesh Sharma wrote:
> > process 5764 is waiting for relation (table) with OID 
> > 16459(table2_primary_key), that table is blocked by process
> 4970 and process 4970 is waiting for a lock on another table, OID 
> 16502(table1), which the first process has a lock
> on.
> > Process 5764: INSERT INTO table2 ( id , roll_number, name) VALUES(
> > '1', '4','abc' ) Process 4970: REINDEX TABLE table1, table2 etc..
> >
> > How to resolve this problem?
> 
> don't do reindex when the tables are in use.
> 
> or
> 
> why does process 5764 have a lock on table 1 while its inserting into table 2?
> 
> or
> 
> do each reindex as a seperate transaction so only one table gets locked at a 
> time.
> 
> --
> john r pierce, recycling bits in santa cruz
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] Request to share information regarding errors

[Charles Clavadetscher]

Hello Yogesh

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Yogesh Sharma
> Sent: Mittwoch, 16. November 2016 07:59
> To: Charles Clavadetscher <clavadetsc...@swisspug.org>; 
> pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding errors
> 
> Dear Charles,
> 
> Thanks for sharing such information.
> First thing, I want to confirm.
> >You are trying to insert a row in a table that has a not-null constraint on 
> >a field.
> >Find the record that causes the error and include a valid value for the 
> >column that has the not null constraint.
> Entry is already deleted from table and value is defined not null in create 
> table query.
> But this type of error is not displaying every time.

Well yes, it only displays if the record that you are trying to insert violates 
the constraint, i.e. if you are trying to assign a
null value to a not-null column. Otherwise it doesn't.

> >A foreign key must match the primary key of the table it refers to. So you 
> >should create the primary key contraint
> on table2 using id and phone_id.
> At this time, DB is shutdown.

If the DB would be shut down, then you would not get any message from it. So 
that cannot be true.

> This shutdown is related to this error or not?

No. The error message states clearly that a table cannot be modified because a 
precondition for that change is not given. As
mentioned above, you would never get a message from the DB if it's not running.

Bye
Charles

> Regards,
> Yogesh
> 
> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Wednesday, November 16, 2016 3:44 PM
> To: Yogesh Sharma; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding errors
> 
> Good morning
> 
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Yogesh Sharma
> > Sent: Mittwoch, 16. November 2016 07:09
> > To: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Request to share information regarding errors
> >
> > Dear All,
> >
> > Thanks for your support.
> >
> > I am using postgresql 9.3.6 rpm in RHEL6 version.
> >
> > In postgresql logs, I have found below error messages.
> > ERROR:  null value in column "column name" violates not-null constraint.
> 
> You are trying to insert a row in a table that has a not-null constraint on a 
> field.
> Find the record that causes the error and include a valid value for the 
> column that has the not null constraint.
> 
> > Second error found:
> > ERROR:  there is no unique constraint matching given keys for referenced 
> > table "table2"
> > alter table1 table_name add constraint fk_key foreign key (id,
> > phone_id) references table2 (id, phone_id) on delete restrict on
> > update restrict;
> 
> A foreign key must match the primary key of the table it refers to. So you 
> should create the primary key contraint
> on table2 using id and phone_id.
> 
> CREATE UNIQUE INDEX table2_pkey ON table2 (id, phone_id); ALTER TABLE table2 
> ADD PRIMARY KEY USING INDEX
> table2_pkey;
> 
> After that you should be able to add the foreign key to table1.
> 
> Hope this helps.
> Charles
> 
> > Could you please share the details of these errors and how to resolve these 
> > errors from logs.
> >
> >
> > Regards,
> > Yogesh
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> > changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] Request to share information regarding errors

[Charles Clavadetscher]

Good morning

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Yogesh Sharma
> Sent: Mittwoch, 16. November 2016 07:09
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding errors
> 
> Dear All,
> 
> Thanks for your support.
> 
> I am using postgresql 9.3.6 rpm in RHEL6 version.
> 
> In postgresql logs, I have found below error messages.
> ERROR:  null value in column "column name" violates not-null constraint.

You are trying to insert a row in a table that has a not-null constraint on a 
field.
Find the record that causes the error and include a valid value for the column 
that has the not null constraint.

> Second error found:
> ERROR:  there is no unique constraint matching given keys for referenced 
> table "table2"
> alter table1 table_name add constraint fk_key foreign key (id, phone_id) 
> references table2 (id, phone_id) on delete
> restrict on update restrict;

A foreign key must match the primary key of the table it refers to. So you 
should create the primary key contraint on table2 using
id and phone_id.

CREATE UNIQUE INDEX table2_pkey ON table2 (id, phone_id);
ALTER TABLE table2 ADD PRIMARY KEY USING INDEX table2_pkey;

After that you should be able to add the foreign key to table1.

Hope this helps.
Charles

> Could you please share the details of these errors and how to resolve these 
> errors from logs.
> 
> 
> Regards,
> Yogesh
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



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

[Charles Clavadetscher]

Hello

On 10/19/2016 04:58 AM, Greg Sabino Mullane wrote:


-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Patrick B <patrickbake...@gmail.com> writes:
...

However, this new database test server doesn't need to have all the data. I
would like to have only the first 100 rows(example) of each table in my
database.

...

This should do what you ask.

If the order does not matter, leave out the ORDER BY.

This assumes everything of interest is in the public schema.

$ createdb testdb
$ pg_dump realdb --schema-only | psql -q testdb
$ psql realdb

psql> \o dump.some.rows.sh
psql> select format($$psql realdb -c 'COPY (select * from %I order by 1 limit 
%s) TO STDOUT' | psql testdb -c 'COPY %I FROM STDIN' $$, table_name, 100, 
table_name)
  from information_schema.tables where table_schema = 'public' and 
table_type = 'BASE TABLE';
psql> \q

$ sh dump.some.rows.sh


I may be overseeing something, but what about dependencies between 
tables, sequencies, indexes, etc.? I guess that if one takes the first 
100 rows of a table referenced by another table, there is no guarantee 
that in the first 100 rows of the referencing table there will not be 
some foreign key that does not exist.


Regards
Charles



- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201610182256
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlgG4NkACgkQvJuQZxSWSsge4ACePhBOBtBFnGNxXt5qpY7X+w3o
d04AoKTzAgxcaqy8qfIE0LPuzG9x0KIU
=sS+m
-END PGP SIGNATURE-






--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Restricted access on DataBases

[Charles Clavadetscher]

Hello

> > Also try this:
> > ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, 
> > DELETE, TRUNCATE, REFERENCES, TRIGGER
> >ON TABLES TO ex_dbuser;
> >
> > You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to 
> > objects created by su and not
> >ex_mainuser, unless you specify it with FOR ex_mainuser.
> >
>
> So... I repeated the test.
> 
> --- login with postgres:
> 
>   CREATE DATABASE db_testrole
> WITH ENCODING='UTF8'
>  TEMPLATE=template0
> CONNECTION LIMIT=-1;
> 
>   CREATE ROLE u_tr_db LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE 
> NOREPLICATION;
> 
> 
>   CREATE ROLE u_tr_main LOGIN
> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
>   GRANT u_tr_db TO u_tr_main;
> 
> 
>   ALTER DATABASE db_testrole
> OWNER TO u_tr_db;
> 
>   REVOKE ALL ON DATABASE db_testrole FROM public;
>   GRANT CREATE, TEMPORARY ON DATABASE db_testrole TO public;
>   GRANT ALL ON DATABASE db_testrole TO u_tr_db;
> 
>   ALTER DEFAULT PRIVILEGES
>   GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER 
> ON TABLES
>   TO u_tr_db;

Here you are telling PostgreSQL to grant those privileges to u_tr_db on tables 
created by user postgres.

>  login with u_tr_main:
> 
>   create table t_canyouseeme_1 (k int);
> 
>  login with u_tr_db:
> 
>   select * from t_canyouseeme_1;
> 
>   ERROR: permission denied for relation t_canyouseeme_1
>   SQL state: 42501
>  As you see before, u_tr_db got all default privileges on future tables, so I 
> don't understand why he don't get to
> "t_canyouseeme_1".

This is not correct. You issued the ALTER DEFAULT PRIVILEGES statement as user 
postgres. So u_tr_db is granted privileges only on tables created by user 
postgres. Since you created the table as user u_tr_main the default privileges 
don't apply, because there are none defined.

> If I try to use these things they would work:
> 
>   A.)
> 
>    login with u_tr_main:
> 
>   set role u_tr_db;
> 
>   create table t_canyouseeme_2 (k int);
>
>    login with u_tr_db:
> 
>   select * from t_canyouseeme_2; -- OK!

Yes, because the owner of the table is u_tr_db. With set role user u_tr_main is 
impersonating user u_tr_db.

>   B.)
> 
>    login with su:
> 
> 
>   ALTER DEFAULT PRIVILEGES FOR role u_tr_main  GRANT INSERT, 
> SELECT, UPDATE, DELETE, TRUNCATE,
> REFERENCES, TRIGGER ON TABLES TO u_tr_db;

Here you are telling PostgreSQL to grant privileges on tables created by 
u_tr_main to u_tr_db.

>    login with u_tr_main:
> 
>   create table t_canyouseeme_3 (k int);
> 
>    login with u_tr_db:
> 
>   select * from t_canyouseeme_3; -- OK!
> 
> 
> A.) is because I can set role to u_tr_db and then he is the creator, he get 
> all rights.
> B.) I don't understand this statement... :-( :-( :-(
> 
> So the main questions.
> Why the default privilege settings aren't affected on newly created table?
> See:
> 
>   ALTER DEFAULT PRIVILEGES
>   GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER 
> ON TABLES
>   TO u_tr_db;

They do if the user creating the table is the user that issued the statement. 
In the case above postgres.

> What are the meaning of this statement if they won't usable for object 
> created by another users?
> U_TR_DB is owner, so they have all privileges for next tables he will create.
> So I supposed that "default privileges" is for future objects created by 
> different users.
> But this not works here.
> 
> I don't understand case B.
> U_TR_MAIN gives all privileges to U_TR_DB for all newly created table?

Yes. You may also choose to restrict the privileges, instead of granting all of 
them.

> What are the differences between?
> 
>   1.  ALTER DEFAULT PRIVILEGES
>   GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER 
> ON TABLES
>   TO u_tr_db;
>   2.  ALTER DEFAULT PRIVILEGES FOR role u_tr_main  GRANT INSERT, SELECT, 
> UPDATE, DELETE, TRUNCATE, REFERENCES,
> TRIGGER ON TABLES TO u_tr_db;

In 1 the rule apply for tables created by the user that created the default 
privileges. Specifically the current_user is the one used for authorization 
checks.
In 2 you say explicitly that the rule applies to tables created by user 
u_tr_main.

> Why the second works and first not?

They both work. In the first statement it works if you create tables as the 
user who was the current_user when you issued the alter default privileges 
statement. In the second it works if you create a table as user u_tr_main.

> ---
> 
> 
>   db_testrole-# \ddp
>   Default access privileges
>  Owner   | Schema | Type  |  Access privileges
>   ---++---+-
>postgres  

Re: [GENERAL] Question on replace function [solved]

[Charles Clavadetscher]

Hello Tom and Adrian

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver
> Sent: Sonntag, 25. September 2016 18:38
> To: Tom Lane <t...@sss.pgh.pa.us>; Charles Clavadetscher 
> <clavadetsc...@swisspug.org>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Question on replace function [solved]
> 
> On 09/25/2016 08:39 AM, Tom Lane wrote:
> > Charles Clavadetscher <clavadetsc...@swisspug.org> writes:
> >> Honestly I still don't understand why this happened this way.
> >
> > I wonder if you have standard_conforming_strings turned off, or did
> > when that data was inserted.  That would change the behavior of
> > backslashes in string literals.
> 
> That got me to thinking:
> 
> standard_conforming_strings = on

I checked the configuration and standard_conforming_strings is on and I did not 
change it before reading the data. The result looks
the same as you showed in the first test.
In a short test, trying to read the file with this setting off leads to other 
errors. Possibly I'll be able to make some more tests
later or in the evening and report.

Thank you for you input.
Charles




-- 
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] Question on replace function [solved]

[Charles Clavadetscher]

Hi Rob

On 09/25/2016 01:39 PM, rob stone wrote:
>
> On Sun, 2016-09-25 at 10:29 +0200, Charles Clavadetscher wrote:
>> Hello
>>
>> I am using PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by
>> gcc
>> (Debian 4.7.2-5) 4.7.2, 64-bit
>>
>> I imported data from a MariaDB table into PostgreSQL and noticed
>> that
>> the content of a field was not correct, but I was not able to change
>> it.
>> The field is called vcard and is of datatye text.
>>
>> The structure of the table:
>>
>> roundcubemail=# \d contacts
>> Tabelle „public.contacts“
>> Spalte   |   Typ|
>> Attribute
>> +--+-
>> ---
>>   contact_id | integer  | not null Vorgabewert
>> nextval(('contacts_seq'::text)::regclass)
>>   changed| timestamp with time zone | not null Vorgabewert now()
>>   del| smallint | not null Vorgabewert 0
>>   name   | character varying(128)   | not null Vorgabewert
>> ''::character varying
>>   email  | text | not null Vorgabewert
>> ''::text
>>   firstname  | character varying(128)   | not null Vorgabewert
>> ''::character varying
>>   surname| character varying(128)   | not null Vorgabewert
>> ''::character varying
>>   vcard  | text |
>>   words  | text |
>>   user_id| integer  | not null
>>
>> The content of vcard looks as follows (replaced real names with
>> placeholders):
>>
>> BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname
>> ;;;\r\\rFN:Firstname
>> Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD
>>
>> My target is to replace all occurences of '\r\\r' with E'\r\n' to
>> comply
>> with RFC 6350.
>>
>> I tried using the function replace and I am faced with a strange
>> behaviour. If I use the function with a string as shown above I get
>> the
>> expected result:
>>
>> elect replace('BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname
>> ;;;\r\\rFN:Firstname
>> Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD','\r\
>> \r',E'\r\n')
>> ;
>>
>>  replace
>> 
>>   BEGIN:VCARD\r +
>>   VERSION:3.0\r +
>>   N:;Firstname Lastname ;;;\r   +
>>   FN:Firstname Lastname\r   +
>>   EMAIL;TYPE=INTERNET;TYPE=OTHER:email\r+
>>   END:VCARD
>> (1 row)
>>
>> However, if I use the function on the vcard field nothing is
>> replaced:
>>
>> select replace(vcard,'\r\\r',E'\r\n') from contacts;
>>
>>
>> replace
>> ---
>> ---
>> 
>>   BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname
>> ;;;\r\\rFN:Firstname
>> Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD
>> (1 row)
>>
>> Does anybody have an idea what I am doing wrong?
>> Thank you for your help.
>>
>> Charles
>>
>> --
>> Swiss PostgreSQL Users Group
>> c/o Charles Clavadetscher
>> Treasurer
>> Motorenstrasse 18
>> CH – 8005 Zürich
>>
>> http://www.swisspug.org
>>
>> +---+
>>>
>>>     __  ___   |
>>>  /)/  \/   \  |
>>> ( / ___\) |
>>>  \(/ o)  ( o)   ) |
>>>   \_  (_  )   \ ) _/  |
>>> \  /\_/\)/|
>>>  \/ |
>>>   _|  |   |
>>>   \|_/|
>>>   |
>>> PostgreSQL 1996-2016  |
>>>  20 Years of Success  |
>>>   |
>> +---+
>>
>>
>
>
>
> Tested this on 9.6beta3 on a test database and it appears to work fine.
>
> Inserted one row.
>
> dinkumerp=> select * from contacts;
> LOG:  duration: 0.571 ms  statement: select * from contacts;
>  contact_id |changed| del | name | email |
> firstname | s
> urname
> |  vcard  |
> words
>  | user_id
> +---+-+--+---+-
> --+--
> ---+--

[GENERAL] Question on replace function

[Charles Clavadetscher]

Hello

I am using PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by gcc 
(Debian 4.7.2-5) 4.7.2, 64-bit


I imported data from a MariaDB table into PostgreSQL and noticed that 
the content of a field was not correct, but I was not able to change it. 
The field is called vcard and is of datatye text.


The structure of the table:

roundcubemail=# \d contacts
   Tabelle „public.contacts“
   Spalte   |   Typ| 
Attribute

+--+
 contact_id | integer  | not null Vorgabewert 
nextval(('contacts_seq'::text)::regclass)

 changed| timestamp with time zone | not null Vorgabewert now()
 del| smallint | not null Vorgabewert 0
 name   | character varying(128)   | not null Vorgabewert 
''::character varying

 email  | text | not null Vorgabewert ''::text
 firstname  | character varying(128)   | not null Vorgabewert 
''::character varying
 surname| character varying(128)   | not null Vorgabewert 
''::character varying

 vcard  | text |
 words  | text |
 user_id| integer  | not null

The content of vcard looks as follows (replaced real names with 
placeholders):


BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname 
;;;\r\\rFN:Firstname 
Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD


My target is to replace all occurences of '\r\\r' with E'\r\n' to comply 
with RFC 6350.


I tried using the function replace and I am faced with a strange 
behaviour. If I use the function with a string as shown above I get the 
expected result:


elect replace('BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname 
;;;\r\\rFN:Firstname 
Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD','\r\\r',E'\r\n') 
;


replace

 BEGIN:VCARD\r +
 VERSION:3.0\r +
 N:;Firstname Lastname ;;;\r   +
 FN:Firstname Lastname\r   +
 EMAIL;TYPE=INTERNET;TYPE=OTHER:email\r+
 END:VCARD
(1 row)

However, if I use the function on the vcard field nothing is replaced:

select replace(vcard,'\r\\r',E'\r\n') from contacts;


replace
--
 BEGIN:VCARD\r\\rVERSION:3.0\r\\rN:;Firstname Lastname 
;;;\r\\rFN:Firstname 
Lastname\r\\rEMAIL;TYPE=INTERNET;TYPE=OTHER:email\r\\rEND:VCARD

(1 row)

Does anybody have an idea what I am doing wrong?
Thank you for your help.

Charles

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Restricted access on DataBases

[Charles Clavadetscher]

Hello

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Durumdara
Sent: Mittwoch, 14. September 2016 17:13
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restricted access on DataBases

 

Dear Adrian and Charles!

 

I tried to create a step by step instruction. 

 

The real commands are separated by ";"

 

Other commands are: "login as username", "use db"

I ran them in PGAdmin with changing the connection to simulate what I feel as 
problem.
I suppused the ex_dbuser have owner rights to the DB, and with "default 
privileges" it must see the new tables created by ex_mainuser. Without them I 
would understand why (inherited role would have diffferent rights on creation).

If I want to represent this in other way, I would say:

- ex_mainuser have all rights as ex_dbuser, but it could have more

- but when ex_dbuser got all rights to future objects, it must see what 
ex_mainuser created on his database

 

If this not happened then my idea crashes, because we must login with ex_dbuser 
to create objects, or we must create all objects by ex_mainuser WITH ONLY OWNER 
SETTING (as ex_dbuser).

 

The example:

 


-- login as su
-- CREATE DATABASE ct_db WITH OWNER = ex_dbuser ENCODING = 'UTF8' TABLESPACE = 
pg_default template = template0;
-- use ct_db

 

-- login as ex_dbuser
-- begin; create table t_dbuser (id integer);commit;

 

-- login as ex_mainuser
-- begin; create table t_mainuser (id integer); commit;

 

-- login as ex_dbuser
-- select * from t_mainuser; -- ERROR!

 

-- login as su
-- ALTER DEFAULT PRIVILEGES  GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, 
REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

 

-- login as ex_mainuser
-- begin; create table t_mainuser2 (id integer); commit;

 

-- login as ex_dbuser
-- select * from t_mainuser2;  -- ERROR!

 

-- login as su
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, SELECT, UPDATE, 
DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

 

-- login as ex_mainuser
-- begin; create table t_mainuser3 (id integer); commit;

 

-- login as ex_dbuser
-- select * from t_mainuser3;  -- ERROR!

 

Thanks: dd

 

As Adrian said, it would help to know what you granted to whom.

Also it would help to know what the exact error message is that you get. In may 
give an idea if you forgot somewhere some privilege.

And last but not least it would help to know who is CURRENT_USER and in which 
DB you are before you execute a statement. I don’t mean what you think it is, 
but what is delivered by

 

SELECT SESSION_USER, CURRENT_USER;

SELECT current_database();

 

Since you are using a graphical tool and submit from some editor queries to the 
DB, I would not assume that you are sending the query to the right database 
with the correct user without checking it out.

 

Could you also provide the result of

 

\ddp

 

from a psql shell (you can open one from pgAdmin: click on the database you 
want to check, in the menu plugins->PSQL Console). This is a list of you custom 
default privileges.

 

Also try this:

 

ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, DELETE, 
TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;

 

You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to objects 
created by su and not ex_mainuser, unless you specify it with FOR ex_mainuser.

 

Besides, if the objects in the table will not be created by the owner, but by 
your admin, then I don’t very much see the point in giving ownership. That 
could be done anyway in the public schema, unless you changed that.

 

I have to leave now, but there are some more things that could be verified

 

Regards

Charles

 

2016-09-14 16:52 GMT+02:00 Adrian Klaver  >:

On 09/14/2016 06:52 AM, Durumdara wrote:

Dear Charles!

 


I thought before this test that mainuser get all rights as dbuser, so it
have rights to the next (future) objects too.
So mainuser and dbuser have equivalent rights in db database.

Thanks for your every info!


In my previous post I mentioned using \dp or \z. The output from those commands 
can be hard to understand without a key, which I forgot to mention. The key can 
be found here:

https://www.postgresql.org/docs/9.5/static/sql-grant.html

in the Notes section.

 


Regards
dd











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

 

Re: [GENERAL] Restricted access on DataBases

[Charles Clavadetscher]

Hello

I did oversee the additional questions...

On 09/07/2016 06:45 PM, Charles Clavadetscher wrote:

Hello

On 09/07/2016 03:24 PM, Durumdara wrote:

Dear Everybody!

I read the documentation based on your example. First reactions.
2016-09-05 18:25 GMT+02:00 Charles Clavadetscher
<clavadetsc...@swisspug.org <mailto:clavadetsc...@swisspug.org>>:


GRANT us_a, us_b, us_c TO main_admin;



Ah, it's good. I can merge the "owner" rights to one. :-)
It's like "doubling"! :-)

Here an example (obviously you will choose secure passwords and
initialize them using \password . This is just a very
simple example). I used 9.5 but it would work with earlier versions
as well.

-- Create roles and databases

CREATE ROLE main_admin LOGIN PASSWORD 'xxx';

CREATE ROLE us_a LOGIN PASSWORD 'xxx';
CREATE DATABASE db_a;
ALTER DATABASE db_a OWNER TO us_a;

CREATE ROLE us_b LOGIN PASSWORD 'xxx';
CREATE DATABASE db_b;
ALTER DATABASE db_b OWNER TO us_b;

-- Restrict access

REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;

REVOKE CONNECT ON DATABASE db_b FROM public;
GRANT CONNECT ON DATABASE db_b TO us_b;

-- Grant all user rights to main_admin:

GRANT us_a, us_b TO main_admin;


What could be the problem with the revoking only "connect" priv? What
are/would be the silent side-effects?


None.

Just before I go on answering your questions, a general statement from
my side. If I understood you correctly you have a set of customers that
each is owner of his database. Other customers are not supposed to look
into his data. But for maintenance reasons you have a main_admin user
that must have the same privileges as your various customers.

So we have distinct databases, not distinct schemas in a single database.


For example:
Ok, us_b can't connect to db_a, but...
He can connect to db_b and may he can start(?) a multidatabase query...


He can't, how should he? In order to access other databases you would
need to set up foreign data wrappers and adjust the privileges on it.
But for your use case there is no need for it. Even if the community
would implement something like multidatabase query natively I would
expect the privileges on the database to hold on.


He can set his role to bla, and he can insert the table db_a.X...


If you are speaking still of us_b, well he can't. A user can only set a
role he belongs to (see documentation). Now if you have a user bla that
has access to db_a and you granted that role to us_b, well, yes he can,
but this is your responsibility. PostgreSQL does not protect you from
doing security design errors.


He can read the temp tables of db_a...


Well, a temp table is usually created within a transaction, so no other
users have access to them anyway. Besides they are created in the
owner's database, so without connect, no way.


He can read the structure of db_a


No. User us_b has access to the structure of db_b not db_a. This is
defined on a database level and not global (like, e.g. roles).


He can break out from his sandbox by...???


AFAIK he can't. But maybe some other specialist will be able to build an
attack vector to it.

So far, I would say that you are on a pretty sound ground and that is
due to a very clean implementation from the community.

Charles




---

Other question:
Can I imagine the GRANT as present, and the DEFAULT PRIVILEGES as future?


It depends. From the requirements that you submitted, you don't need to 
alter default privileges. Your single user, let's say us_a, can create 
objects, including schemas and then tables in that schema within db_a 
and your main_admin will be able to access them via the grant of role 
us_a to him.



Your two solutions are seem to be better like "revoke public in all and
grant all rights in all object in the present (GRANT) and in the future
(DEF. PRIV)".


No. You need to change the default privileges in other scenarios. If the 
explanations so far don't match your requirements, I may have 
misunderstood what you are trying to achieve. In that case send please a 
more clarifying use case.


Charles



Very-very thank you!

dd




--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Restricted access on DataBases

[Charles Clavadetscher]

Hello

On 09/07/2016 03:24 PM, Durumdara wrote:

Dear Everybody!

I read the documentation based on your example. First reactions.
2016-09-05 18:25 GMT+02:00 Charles Clavadetscher
<clavadetsc...@swisspug.org <mailto:clavadetsc...@swisspug.org>>:


GRANT us_a, us_b, us_c TO main_admin;



Ah, it's good. I can merge the "owner" rights to one. :-)
It's like "doubling"! :-)

Here an example (obviously you will choose secure passwords and
initialize them using \password . This is just a very
simple example). I used 9.5 but it would work with earlier versions
as well.

-- Create roles and databases

CREATE ROLE main_admin LOGIN PASSWORD 'xxx';

CREATE ROLE us_a LOGIN PASSWORD 'xxx';
CREATE DATABASE db_a;
ALTER DATABASE db_a OWNER TO us_a;

CREATE ROLE us_b LOGIN PASSWORD 'xxx';
CREATE DATABASE db_b;
ALTER DATABASE db_b OWNER TO us_b;

-- Restrict access

REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;

REVOKE CONNECT ON DATABASE db_b FROM public;
GRANT CONNECT ON DATABASE db_b TO us_b;

-- Grant all user rights to main_admin:

GRANT us_a, us_b TO main_admin;


What could be the problem with the revoking only "connect" priv? What
are/would be the silent side-effects?


None.

Just before I go on answering your questions, a general statement from 
my side. If I understood you correctly you have a set of customers that 
each is owner of his database. Other customers are not supposed to look 
into his data. But for maintenance reasons you have a main_admin user 
that must have the same privileges as your various customers.


So we have distinct databases, not distinct schemas in a single database.


For example:
Ok, us_b can't connect to db_a, but...
He can connect to db_b and may he can start(?) a multidatabase query...


He can't, how should he? In order to access other databases you would 
need to set up foreign data wrappers and adjust the privileges on it. 
But for your use case there is no need for it. Even if the community 
would implement something like multidatabase query natively I would 
expect the privileges on the database to hold on.



He can set his role to bla, and he can insert the table db_a.X...


If you are speaking still of us_b, well he can't. A user can only set a 
role he belongs to (see documentation). Now if you have a user bla that 
has access to db_a and you granted that role to us_b, well, yes he can, 
but this is your responsibility. PostgreSQL does not protect you from 
doing security design errors.



He can read the temp tables of db_a...


Well, a temp table is usually created within a transaction, so no other 
users have access to them anyway. Besides they are created in the 
owner's database, so without connect, no way.



He can read the structure of db_a


No. User us_b has access to the structure of db_b not db_a. This is 
defined on a database level and not global (like, e.g. roles).



He can break out from his sandbox by...???


AFAIK he can't. But maybe some other specialist will be able to build an 
attack vector to it.


So far, I would say that you are on a pretty sound ground and that is 
due to a very clean implementation from the community.


Charles




---

Other question:
Can I imagine the GRANT as present, and the DEFAULT PRIVILEGES as future?

Your two solutions are seem to be better like "revoke public in all and
grant all rights in all object in the present (GRANT) and in the future
(DEF. PRIV)".

Very-very thank you!

dd


--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Check if there 6 last records of same type without gaps

[Charles Clavadetscher]

Hello

> On 06.09.2016, at 14:35, Alexander Farber  wrote:
> 
> No, I am sorry - for struggling with probably basic questions, but without 
> GROUP BY I get another error:
> 
> org.postgresql.util.PSQLException: ERROR: column "words_moves.played" must 
> appear in the GROUP BY clause or be used in an aggregate function|  
> Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL 
> statement

You must group by played, as the message suggests. You are implicitly selecting 
the column through order by, although you don't have it in the list of selected 
columns.

Charles

> 
> 
>> On Tue, Sep 6, 2016 at 2:30 PM, Sándor Daku  wrote:
>>> On 6 September 2016 at 14:23, Alexander Farber  
>>> wrote:
>>> 
 On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku  wrote:
 
 Get the last 6 record and 
 
 1.  ... action='SKIP' as isskip ... then you can group on and count the 
 skip moves. If there is 6 of them the game ends.
 
 2.  ... sum(case when action='SKIP' then 1 else 0 end) ... If the result 
 is 6 the game ends
>>> 
>>> SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
>>> FROM words_moves
>>> GROUP BY action
>>> ORDER BY played DESC
>>> LIMIT 6
>>> INTO _sum;
>>> 
>>> RAISE NOTICE '_sum = %', _sum;
>>> 
>>> IF _sum = 6 THEN
>>> _finished = CURRENT_TIMESTAMP;
>>> END IF;
>>> 
>>> but get the error -
>>> 
>>> org.postgresql.util.PSQLException: ERROR: 
>>> column "words_moves.played" must appear in the GROUP BY clause or be used 
>>> in an aggregate function|  
>>> Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL 
>>> statement
>  
>>> P.S: Here is the table in question
>>> 
>>>   Table "public.words_moves"
>>>  Column |   Type   | Modifiers
>>> +--+---
>>>  mid| integer  | not null default 
>>> nextval('words_moves_mid_seq'::regclass)
>>>  action | words_action | not null
>>>  gid| integer  | not null
>>>  uid| integer  | not null
>>>  played | timestamp with time zone | not null
>>>  tiles  | jsonb|
>>>  score  | integer  |
>>> Indexes:
>>> "words_moves_pkey" PRIMARY KEY, btree (mid)
>>> Check constraints:
>>> "words_moves_score_check" CHECK (score > 0)
>>> Foreign-key constraints:
>>> "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON 
>>> DELETE CASCADE
>>> "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON 
>>> DELETE CASCADE
>>> Referenced by:
>>> TABLE "words_games" CONSTRAINT "words_mid_fk" FOREIGN KEY (mid) 
>>> REFERENCES words_moves(mid)
>>> TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY 
>>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>> 
>> Sry! I wasn't clear enough.
>> 
>> Those are two separate solutions. Pick one!
>> 
>> In this case you don't need the group by 
>> 
>> SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
>> FROM words_moves
>> ORDER BY played DESC
>> LIMIT 6
>> INTO _sum

Re: [GENERAL] Restricted access on DataBases

[Charles Clavadetscher]

Hello

On 09/05/2016 05:56 PM, Charles Clavadetscher wrote:

Hello

On 09/05/2016 04:19 PM, Adrian Klaver wrote:

On 09/05/2016 05:45 AM, Durumdara wrote:

Dear PG-masters!

We want to put more databases to one server, to "public" schema:
DB_A, DB_B, DB_C.


The PUBLIC schema is contained within a database not the other way
around, so further explanation is necessary.


And users:
US_A, US_B, US_C, and Main_Admin.
We want to setup the environment.
Every simple user can access his database:
DB_A - US_A
DB_B - US_B
DB_C - US_C

They can't access other databases only theirs.


When use speak of "their database", do you mean that they are the owner
of it or that they simply should have specific privileges?

If not, is main_admin the owner of all databases?


Main_Admin can access all databases.


Is Main_Admin created as a superuser?

If not what role attributes does it have?



I'm not sure how to do it perfectly.
We tried to remove "public" role, and add US_A to DB_A.
But the subobjects (table named "teszt") aren't accessable.


How did you specify GRANTing permissions on DB_A to US_A?

You might to want to look at the privileges that are provided to various
objects by GRANT:

https://www.postgresql.org/docs/9.5/static/sql-grant.html


Yes, read this document, it helps a lot.

Pragmatically I find a simple way to restrict access to a database is to
revoke CONNECT on it from public and then GRANT CONNECT and, if
necessary, privileges on objects in that database to the legitimate
user(s):

REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;

This will still not free you from managing the privileges on the objects
created. If main_admin is a superuser it will hav.e access to everything
anyway and you don't need to manage grants for it. If not, as Adrian
said, and assuming in db_a, only us_a will create objects, you will have
to alter the default privileges of us_a to grant privileges to
main_admin. This must be done for each database, i.e. db_b, db_c, etc.



GRANT on Database Objects

For instance

CREATE

For databases, allows new schemas to be created within the database.




I can reown DB_A to US_A, but this revoke all rights from Main_Admin.


Hard to answer until we know what permissions Main_Admin has.



What is the simple way to we can avoid the access from another users,
but give needed rights to DB_[n] and Main_Admin? (Tables, Sequences,
etc).

And how we keep this state later? For example: DB_A creates a new table.
Main_Admin must access this automatically...


Defualt privileges:

https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html




I don't understand this area properly. For me the "public" means "access
for all users", which isn't good (DB_A vs. US_C).


Actually it is not as broad as that.

https://www.postgresql.org/docs/9.5/static/sql-grant.html

"PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default privileges
granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for
databases; EXECUTE privilege for functions; and USAGE privilege for
languages. The object owner can, of course, REVOKE both default and
expressly granted privileges. (For maximum security, issue the REVOKE in
the same transaction that creates the object; then there is no window in
which another user can use the object.) Also, these initial default
privilege settings can be changed using the ALTER DEFAULT PRIVILEGES
command.

"


As I think we can't mix the rights (Main_Admin = US_A + US_B  +
US_C...).


Actually you could:

GRANT us_a, us_b, us_c TO main_admin;



Here an example (obviously you will choose secure passwords and 
initialize them using \password . This is just a very simple 
example). I used 9.5 but it would work with earlier versions as well.


-- Create roles and databases

CREATE ROLE main_admin LOGIN PASSWORD 'xxx';

CREATE ROLE us_a LOGIN PASSWORD 'xxx';
CREATE DATABASE db_a;
ALTER DATABASE db_a OWNER TO us_a;

CREATE ROLE us_b LOGIN PASSWORD 'xxx';
CREATE DATABASE db_b;
ALTER DATABASE db_b OWNER TO us_b;

-- Restrict access

REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;

REVOKE CONNECT ON DATABASE db_b FROM public;
GRANT CONNECT ON DATABASE db_b TO us_b;

-- Grant all user rights to main_admin:

GRANT us_a, us_b TO main_admin;

Test:

-- Connect as us_a to db_a:

charles@charles.localhost=# \c db_a us_a
Password for user us_a:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
bits: 256, compression: off)

You are now connected to database "db_a" as user "us_a".

-- Create a table and enter some data:

us_a@db_a.localhost=> CREATE TABLE test (id INTEGER, tst TEXT);
CREATE TABLE
us_a@db_a.localhost=> INSERT INTO test VALUES (1,'Blabla');
INSERT 0 

Re: [GENERAL] Restricted access on DataBases

[Charles Clavadetscher]

Hello

On 09/05/2016 04:19 PM, Adrian Klaver wrote:

On 09/05/2016 05:45 AM, Durumdara wrote:

Dear PG-masters!

We want to put more databases to one server, to "public" schema:
DB_A, DB_B, DB_C.


The PUBLIC schema is contained within a database not the other way
around, so further explanation is necessary.


And users:
US_A, US_B, US_C, and Main_Admin.
We want to setup the environment.
Every simple user can access his database:
DB_A - US_A
DB_B - US_B
DB_C - US_C

They can't access other databases only theirs.


When use speak of "their database", do you mean that they are the owner 
of it or that they simply should have specific privileges?


If not, is main_admin the owner of all databases?


Main_Admin can access all databases.


Is Main_Admin created as a superuser?

If not what role attributes does it have?



I'm not sure how to do it perfectly.
We tried to remove "public" role, and add US_A to DB_A.
But the subobjects (table named "teszt") aren't accessable.


How did you specify GRANTing permissions on DB_A to US_A?

You might to want to look at the privileges that are provided to various
objects by GRANT:

https://www.postgresql.org/docs/9.5/static/sql-grant.html


Yes, read this document, it helps a lot.

Pragmatically I find a simple way to restrict access to a database is to 
revoke CONNECT on it from public and then GRANT CONNECT and, if 
necessary, privileges on objects in that database to the legitimate user(s):


REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;

This will still not free you from managing the privileges on the objects 
created. If main_admin is a superuser it will hav.e access to everything 
anyway and you don't need to manage grants for it. If not, as Adrian 
said, and assuming in db_a, only us_a will create objects, you will have 
to alter the default privileges of us_a to grant privileges to 
main_admin. This must be done for each database, i.e. db_b, db_c, etc.




GRANT on Database Objects

For instance

CREATE

For databases, allows new schemas to be created within the database.




I can reown DB_A to US_A, but this revoke all rights from Main_Admin.


Hard to answer until we know what permissions Main_Admin has.



What is the simple way to we can avoid the access from another users,
but give needed rights to DB_[n] and Main_Admin? (Tables, Sequences,
etc).

And how we keep this state later? For example: DB_A creates a new table.
Main_Admin must access this automatically...


Defualt privileges:

https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html



I don't understand this area properly. For me the "public" means "access
for all users", which isn't good (DB_A vs. US_C).


Actually it is not as broad as that.

https://www.postgresql.org/docs/9.5/static/sql-grant.html

"PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default privileges
granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for
databases; EXECUTE privilege for functions; and USAGE privilege for
languages. The object owner can, of course, REVOKE both default and
expressly granted privileges. (For maximum security, issue the REVOKE in
the same transaction that creates the object; then there is no window in
which another user can use the object.) Also, these initial default
privilege settings can be changed using the ALTER DEFAULT PRIVILEGES
command.

"


As I think we can't mix the rights (Main_Admin = US_A + US_B  + US_C...).


Actually you could:

GRANT us_a, us_b, us_c TO main_admin;

Now, if you have time for it, I would suggest that you take it to read 
about the roles and privileges system in PostgreSQL. This will strongly 
help you understanding what you are doing.


Charles



Thank you for the help. information, or an example!

DD






--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Upgrading using pg_dumpall

[Charles Clavadetscher]

Hi

On 09/04/2016 05:16 PM, Charles Clavadetscher wrote:

Hi

On 09/04/2016 05:14 PM, Rich Shepard wrote:

On Sat, 3 Sep 2016, Adrian Klaver wrote:


Or if you are fine running the 9.5 instance at port 5432, what happens
if you do?:

psql -d postgres -U some_user -p 5432


$ psql -d postgres -U rshepard -p 5432
Password for user rshepard: FATAL:  password authentication failed for
user "rshepard"
DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

but,

$ psql -d postgres -U postgres -p 5432
Password for user postgres: psql (9.5.4)
Type "help" for help.

postgres=#

  So, it appears to be a password issue. Please advise me how to get the
running postmaster process to accept my accessing databases without a
password being required.


Adrian already answered that. You can set the connection authentication
method to trust. This can have some risks, however.


You may also consider using a .pgpass file:

https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html

Bye
Charles



Bye
Charles



Thanks again,

Rich






--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Upgrading using pg_dumpall

[Charles Clavadetscher]

Hi

On 09/04/2016 05:14 PM, Rich Shepard wrote:

On Sat, 3 Sep 2016, Adrian Klaver wrote:


Or if you are fine running the 9.5 instance at port 5432, what happens
if you do?:

psql -d postgres -U some_user -p 5432


$ psql -d postgres -U rshepard -p 5432
Password for user rshepard: FATAL:  password authentication failed for
user "rshepard"
DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

but,

$ psql -d postgres -U postgres -p 5432
Password for user postgres: psql (9.5.4)
Type "help" for help.

postgres=#

  So, it appears to be a password issue. Please advise me how to get the
running postmaster process to accept my accessing databases without a
password being required.


Adrian already answered that. You can set the connection authentication 
method to trust. This can have some risks, however.


Bye
Charles



Thanks again,

Rich




--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Upgrading using pg_dumpall

[Charles Clavadetscher]

Hello

On 09/04/2016 05:11 PM, Rich Shepard wrote:

On Sun, 4 Sep 2016, Adrian Klaver wrote:


But the message you sent me offlist showed the 9.5 instance running.


  But now it's not running.


How are you starting the instance?


  As superuser poostgres: pg_ctl start -D /var/lib/pgsql/data &

  After removing an orphaned postmaster.pid the above seemed to have
started
postgres, but there's no postmaster process running.


Are you sure that the password being asked for is not for the OS user
you are using to run whatever start script you are using?


  Thinking postmaster is running I tried this:

$ psql crm
Password: FATAL:  password authentication failed for user "rshepard"
DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"


Well, there you have it. As Adrian suggested you may set temporarily the 
authentication method to trust, set yourself a password and change it 
back to md5.


That should do.
Bye
Charles



  The crm database is owned by me.


I have never used it, but I am pretty sure that is not what -W means. It
looks to me that it asks you to create a password at init for the
database
superuser(in this case postgres) and only that user when that user tries
to use log into a database after the cluster is started.


  From man initdb:

-W, --pwprompt
   Makes initdb prompt for a password to give the database
superuser.
   If you don't plan on using password authentication, this is not
   important. Otherwise you won't be able to use password
   authentication until you have a password set up.

  The superuser already exists in /etc/passwd.


Do you remember what password you specified?


  Yes. It's the same password I use for logging in as a user. If it's the
superuser password being requested, then that's the same as my user
password.

  The Slackware rc.postgresql file for 9.5 has changed from 9.3 and
earlier.
It's asking for passwords:

 if [ ! -e $DATADIR/PG_VERSION ]; then
echo "You should initialize the PostgreSQL database
at location $DATADIR"
echo "e.g. su postgres -c \"initdb -D $DATADIR
--locale=en_US.UTF-8 -A md5 -W\""
exit 6
 fi

  Note the '-W' at the end. But, I ran initdb from the command line as user
postgres.

Rich






--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Upgrading using pg_dumpall

[Charles Clavadetscher]

Hello

On 09/04/2016 01:16 AM, Rich Shepard wrote:

On Sat, 3 Sep 2016, Adrian Klaver wrote:


Or if you are fine running the 9.5 instance at port 5432, what happens
if you do?:

psql -d postgres -U some_user -p 5432


$ psql -d postgres -U rshepard -p 5432
Password for user rshepard: psql: FATAL:  password authentication failed
for user "rshepard"


Does the user rshepard exist in the new 9.5 instance?




What was the error message?


  As above.

Thanks,

Rich




--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
> Sent: Mittwoch, 10. August 2016 14:54
> To: pgsql-general 
> Subject: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is 
> ambiguous
> 
> Good afternoon,
> 
> 
> In PostgreSQL 9.5.3 I have created a function (full source code at the 
> bottom), which goes through an 15 x 15
> varchar array and collects words played horizontally and vertically.
> 
> 
> I have declared the function as:
> 
> 
> CREATE OR REPLACE FUNCTION words_check_words(
> IN in_uid integer,
> IN in_gid integer,
> IN in_tiles jsonb)
> RETURNS TABLE(word varchar, score integer) AS $func$ 
> CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT 
> DROP; ...
> SELECT word, max(score) as score FROM _words GROUP BY word; END 
> $func$ LANGUAGE plpgsql;
> 
> 
> And when I call it as:
> 
> 
> select * from words_check_words(2, 1,
> '[{"col":11,"letter":"A","row":8,"value":1},{"col":11,"letter":"B","row":7,"value":3}]'::jsonb);
> 
> 
> then it fails with:
> 
> 
> ERROR:  column reference "word" is ambiguous LINE 1: SELECT word, max(score) 
> as score FROM _words GROUP BY word
>^
> DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
> 
> 
> As I understand the "word" is used both by the RETURN TYPE and my TEMP TABLE.
> 
> 
> How to resolve this "naming conflict" best or maybe there is some better way 
> like using some "internal" table
> implicitly created by the type declaration?
> 
> 
> Thank you
> 
> Alex
> 
> 
> P.S. Below is my full source code and the full log output -
> 
> 

You can use 

#variable_conflict [use_column|use_variable] before BEGIN:

- 
http://dba.stackexchange.com/questions/105831/naming-conflict-between-function-parameter-and-result-of-join-with-using-clause
- https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

Hope this helps.
Regards
Charles

> CREATE OR REPLACE FUNCTION words_check_words(
> IN in_uid integer,
> IN in_gid integer,
> IN in_tiles jsonb)
> RETURNS TABLE(word varchar, score integer) AS $func$ DECLARE
> _tilejsonb;
> _letter  varchar;
> _letter2 varchar;
> _value   integer;
> _value2  integer;
> _col integer;
> _col2integer;
> _row integer;
> _row2integer;
> _letters varchar[][];
> _values  integer[][];
> _multvarchar[][];
> _factor  integer;
> _score   integer;
> _wordvarchar;
> BEGIN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid  AND
> g.player1 = in_uid AND
> -- and it is first player's turn
> (g.played1 IS NULL OR g.played1 < g.played2);
> 
> IF NOT FOUND THEN
> SELECT
> g.letters,
> g.values,
> b.mult
> INTO
> _letters,
> _values,
> _mult
> FROM words_games g, words_boards b WHERE
> g.gid = in_gid AND
> g.bid = b.bid  AND
> g.player2 = in_uid AND
> -- and it is first player's turn
> (g.played2 IS NULL OR g.played2 < g.played1);
> END IF;
> 
> IF NOT FOUND THEN
> RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
> END IF;
> 
> CREATE TEMPORARY TABLE _words (word varchar, score integer) ON COMMIT 
> DROP;
> 
> FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
> LOOP
> _letter :=  _tile->>'letter';
> _value  := (_tile->>'value')::int;
> _col:= (_tile->>'col')::int + 1;
> _row:= (_tile->>'row')::int + 1;
> 
> _letters[_col][_row] := _letter;
> -- multiply the new letter value with premium
> _values[_col][_row]  := _value * 
> words_letter_mult(_mult[_col][_row]);
> 
> _word   := _letter;
> _score  := _values[_col][_row];
> _factor := words_word_mult(_mult[_col][_row]);
> 
> -- go left and prepend letters
> FOR _col2 IN REVERSE (_col - 1)..1 LOOP
> _letter2 := _letters[_col2][_row];
> EXIT WHEN _letter2 IS NULL;
>  

Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

[Charles Clavadetscher]

Hello Alexander

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
> Sent: Montag, 8. August 2016 10:21
> Cc: pgsql-general 
> Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works 
> on Mac, Linux
> 
> Hello Charles, unfortunately on Windows 7 this fails:
> 
> psql (9.5.3)
> Type "help" for help.
> 
> # select lower(('И'::text collate "en_US")) ;
> ERROR:  collation "en_US" for encoding "UTF8" does not exist LINE 1: select 
> lower(('?'::text collate "en_US")) ;

I assume that you did not create the collation yet as I mentioned in a previous 
mail.

kofadmin@test.localhost=> CREATE COLLATION "en_US" (LOCALE = 'English_United 
States.1252');
CREATE COLLATION

Which locale can be created depends on those available on your OS.

Bye
Charles

> By the way I the following code works well for me on all 3 platforms:
> 
> CREATE TABLE words_verbs (
> word varchar(255) PRIMARY KEY CHECK (
> word ~ '^[А-Я]{2,}$' AND
> word !~ '[ЖШ]Ы' AND
> word !~ '[ЧЩ]Я' AND
> word !~ 'Ц[ЮЯ]' AND
> (word ~ '[ТЧ]ЬСЯ$' OR
>  word ~ '[ТЧ]Ь$' OR
>  word ~ 'ТИ$')),
> hashed varchar(32) NOT NULL
> );
> 
> 
> but I understand that it is probably different methods on the lower layer 
> (pcre instead of some collating
> functions?)
> 
> 
> Regards
> 
> Alex




-- 
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] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

[Charles Clavadetscher]

Hello

> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
> Sent: Montag, 8. August 2016 10:12
> Cc: pgsql-general 
> Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works 
> on Mac, Linux
>
> Thank you for the replies.
> On CentOS 7 Linux with pgdg 9.5.3 the lower() method works and I have:
>
> # \l
>  List of databases
>   Name|  Owner   | Encoding |   Collate   |Ctype|
> ---+--+--+-+-+
> postgres  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>   |  |  | | |
> template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>   |  |  | | |
> words | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

Well yes, anogher collation and ctype.

I found an easier way to use the workaround after create collation:

kofadmin@test.localhost=> select lower(('И'::text collate "en_US")) ;
 lower
---
 и
(1 row)

Maybe other more expert than me on this topic will suggest better solution.
Bye
Charles




-- 
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] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Montag, 8. August 2016 09:30
> To: 'Alexander Farber' <alexander.far...@gmail.com>; 'pgsql-general' 
> <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works 
> on Mac, Linux
> 
> Hello
> 
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander
> > Farber
> > Sent: Montag, 8. August 2016 09:10
> > To: pgsql-general <pgsql-general@postgresql.org>
> > Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows,
> > but works on Mac, Linux
> >
> > More info:
> >
> > # \l+
> >
> >   Name|  Owner  | Encoding | Collate | Ctype |
> > ---+-+--+-+---+
> > postgres  | user1   | UTF8 | C   | C |
> > template0 | user1   | UTF8 | C   | C |
> >   | |  | |   |
> > template1 | user1   | UTF8 | C   | C |
> >   | |  | |   |
> > words | user1   | UTF8 | C   | C |
> 
> 
> I cannot test on 9.5, which you are using, but I was able to reproduce the 
> problem on 9.3:
> 
> kofadmin@kofdb.localhost=> CREATE DATABASE test TEMPLATE template0 ENCODING 
> 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C';
> CREATE DATABASE kofadmin@kofdb.localhost=> \l
>  List of databases
>Name|  Owner   | Encoding |  Collate   |   
> Ctype|   Access privileges
> ---+--+--+++---
>  kofdb | kofadmin | UTF8 | English_United States.1252 | 
> English_United States.1252 |
>  test  | kofadmin | UTF8 | C  | C 
>  |
> 
> kofadmin@kofdb.localhost=> \c test
> psql (9.6devel, server 9.3.6)
> You are now connected to database "test" as user "kofadmin".
> kofadmin@test.localhost=> select lower('P');  <-- This works (latin letters)  
> lower
> ---
>  p
> (1 row)
> 
> kofadmin@test.localhost=> select lower('И'); <-- This does not work 
> (cyrillic)  lower
> ---
>  И
> (1 row)
> 
> kofadmin@test.localhost=> \c kofdb
> psql (9.6devel, server 9.3.6)
> You are now connected to database "kofdb" as user "kofadmin".
> kofadmin@kofdb.localhost=> select lower('И'); <-- This works on a DB with 
> another collation and ctype  lower
> ---
>  и
> (1 row)
> 
> It seems to be a problem with collation and or ctype.
> 
> What are the settings of the database on your Linux system where all works 
> correct?

A possible workaround:

kofadmin@test.localhost=> CREATE COLLATION "en_US" (LOCALE = 'English_United 
States.1252');
CREATE COLLATION

kofadmin@test.localhost=> select * from pg_collation;
 collname | collnamespace | collowner | collencoding |collcollate   
  | collctype
--+---+---+--++
 default  |11 |10 |   -1 |  
  |
 C|11 |10 |   -1 | C
  | C
 POSIX|11 |10 |   -1 | POSIX
  | POSIX
 en_US|  2200 | 16394 |6 | English_United 
States.1252 | English_United States.1252
(4 rows)

kofadmin@test.localhost=> with x as (select 'И'::text collate "en_US" as 
letter) select lower(letter) from x ;
 lower
---
 и
(1 row)

Sure, bit uncomfortable.
Bye
Charles

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



-- 
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] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux

[Charles Clavadetscher]

Hello

> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
> Sent: Montag, 8. August 2016 09:10
> To: pgsql-general 
> Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works 
> on Mac, Linux
>
> More info:
>
> # \l+
>   
>   Name|  Owner  | Encoding | Collate | Ctype |
> ---+-+--+-+---+
> postgres  | user1   | UTF8 | C   | C |
> template0 | user1   | UTF8 | C   | C |
>   | |  | |   |
> template1 | user1   | UTF8 | C   | C |
>   | |  | |   |
> words | user1   | UTF8 | C   | C |


I cannot test on 9.5, which you are using, but I was able to reproduce the 
problem on 9.3:

kofadmin@kofdb.localhost=> CREATE DATABASE test TEMPLATE template0 ENCODING 
'UTF8' LC_COLLATE 'C' LC_CTYPE 'C';
CREATE DATABASE
kofadmin@kofdb.localhost=> \l
 List of databases
   Name|  Owner   | Encoding |  Collate   |   Ctype 
   |   Access privileges
---+--+--+++---
 kofdb | kofadmin | UTF8 | English_United States.1252 | English_United 
States.1252 |
 test  | kofadmin | UTF8 | C  | C   
   |

kofadmin@kofdb.localhost=> \c test
psql (9.6devel, server 9.3.6)
You are now connected to database "test" as user "kofadmin".
kofadmin@test.localhost=> select lower('P');  <-- This works (latin letters)
 lower
---
 p
(1 row)

kofadmin@test.localhost=> select lower('И'); <-- This does not work (cyrillic)
 lower
---
 И
(1 row)

kofadmin@test.localhost=> \c kofdb
psql (9.6devel, server 9.3.6)
You are now connected to database "kofdb" as user "kofadmin".
kofadmin@kofdb.localhost=> select lower('И'); <-- This works on a DB with 
another collation and ctype
 lower
---
 и
(1 row)

It seems to be a problem with collation and or ctype.

What are the settings of the database on your Linux system where all works 
correct?

Bye
Charles




-- 
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] Extract data from JSONB

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Michael Paquier
> Sent: Montag, 8. August 2016 05:24
> To: Alex Magnum 
> Cc: Postgres General 
> Subject: Re: [GENERAL] Extract data from JSONB
> 
> On Mon, Aug 8, 2016 at 12:08 PM, Alex Magnum  wrote:
> > How can I convert that into one row each based on status; for example
> > if I only want to have the active modules.
> 
> You can use jsonb_each to decompose that:
> =# select key, (value::json)->'status' from jsonb_each('{
> 
>  "accounts":
>   {"status": true},
>  "admin":{"status": true},
> "calendar": {"status": false},
> "chat": {"status": true},
> "contacts": {"status": true},
> "dashboard":{"status": false},
> "help": {"status": true}}'::jsonb);
> key| ?column?
> ---+--
>  chat  | true
>  help  | true
>  admin | true
>  accounts  | true
>  calendar  | false
>  contacts  | true
>  dashboard | false
> (7 rows)

Building on that you just need to add a where clause, but I assume that was 
obvious.

select key, (value::json)->'status' from jsonb_each(
'{
  "accounts": {"status": true},
  "admin":{"status": true},
  "calendar": {"status": false},
  "chat": {"status": true},
  "contacts": {"status": true},
  "dashboard":{"status": false},
  "help": {"status": true}}'::jsonb)
where (value::json)->>'status' = 'true';

   key| ?column?
--+--
 chat | true
 help | true
 admin| true
 accounts | true
 contacts | true
(5 rows)

or

select key, (value::json)->'status' from jsonb_each(
'{
  "accounts": {"status": true},
  "admin":{"status": true},
  "calendar": {"status": false},
  "chat": {"status": true},
  "contacts": {"status": true},
  "dashboard":{"status": false},
  "help": {"status": true}}'::jsonb)
where ((value::json)->>'status')::boolean;

   key| ?column?
--+--
 chat | true
 help | true
 admin| true
 accounts | true
 contacts | true
(5 rows)

Regards
Charles




> --




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



-- 
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] Log all queries before migration ?

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of ben.play
> Sent: Mittwoch, 3. August 2016 15:29
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Log all queries before migration ?
> 
> Hi guys,
> 
> In fact, I would like to copy a table and then, when everything is finish, 
> execute all inserts and updates since the
> beginning of the copy.
> 
> The biggest problem with the log at this time it's the query.
> All my queries are not runnable.

This may help you?

http://laurenz.github.io/pgreplay/

Regards
Charles

> 
> When I log, I see this kind of queries :
> "duration: 0.046 ms  parse : UPDATE user SET money = money + $1 
> WHERE id = $2"
> 
> But I would like to have only : "UPDATE user SET money = money + 100 WHERE id 
> = 1281012"
> 
> This is our postgresql.conf :
> log_destination = 'csvlog'
> logging_collector = on
> log_min_duration_statement = 0
> log_statement = mod
> log_directory = '/media/hdd/log_queries'
> log_filename='log_insert.log'
> log_file_mode = 0600
> log_error_verbosity = verbose
> 
> 
> What do we have to change ?
> 
> Thank you a lot for your help !
> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/Log-all-queries-before-migration-tp5913922p5914599.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



-- 
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] Proposal "stack trace" like debugging option in PostgreSQL

[Charles Clavadetscher]

Hello

On 08/01/2016 12:15 AM, Tom Lane wrote:

Edson Richter <edsonrich...@hotmail.com> writes:

From: t...@sss.pgh.pa.us
Uh, doesn't the CONTEXT field of error messages give you that already?



Would you give me an example where I can get the info you mention above? Do I 
need to enable some kind of parameter to get this context field?


Well, you have not mentioned what sort of client environment you are
using, but the server certainly sends that information.  In psql,
for example, I can do this:

regression=# create table foo (f1 int primary key, f2 text);
CREATE TABLE
regression=# create function ifoo(int, text) returns void as
$$ begin insert into foo values($1,$2); end $$ language plpgsql;
CREATE FUNCTION
regression=# create function ifoo2(int, text) returns void as
$$ begin perform ifoo($1,$2); end $$ language plpgsql;
CREATE FUNCTION
regression=# select ifoo2(1,'foo');
 ifoo2
---

(1 row)

regression=# select ifoo2(1,'foo');
ERROR:  duplicate key value violates unique constraint "foo_pkey"
DETAIL:  Key (f1)=(1) already exists.
CONTEXT:  SQL statement "insert into foo values($1,$2)"
PL/pgSQL function ifoo(integer,text) line 1 at SQL statement
SQL statement "SELECT ifoo($1,$2)"
PL/pgSQL function ifoo2(integer,text) line 1 at PERFORM

If you're using a misdesigned client that will not show these auxiliary
error fields, you could try looking in the server log --- at default
log verbosity, it will contain that info too.  The above test case
gave me this log entry:

ERROR:  duplicate key value violates unique constraint "foo_pkey"
DETAIL:  Key (f1)=(1) already exists.
CONTEXT:  SQL statement "insert into foo values($1,$2)"
PL/pgSQL function ifoo(integer,text) line 1 at SQL statement
SQL statement "SELECT ifoo($1,$2)"
PL/pgSQL function ifoo2(integer,text) line 1 at PERFORM
STATEMENT:  select ifoo2(1,'foo');

regards, tom lane




Maybe this could help, too.

In the documentation there is a description of how to get the call stack 
of functions:


https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html

40.6.7. Obtaining Current Execution Information

The GET [ CURRENT ] DIAGNOSTICS command retrieves information about 
current execution state (whereas the GET STACKED DIAGNOSTICS command 
discussed above reports information about the execution state as of a 
previous error). This command has the form:


GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

Currently only one information item is supported. Status item PG_CONTEXT 
will return a text string with line(s) of text describing the call 
stack. The first line refers to the current function and currently 
executing GET DIAGNOSTICS command. The second and any subsequent lines 
refer to calling functions further up the call stack. For example:


CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
  RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
  stack text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE E'--- Call Stack ---\n%', stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Call Stack ---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
 
   1
(1 row)

Regards
Charles

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Array value from table as parameter

[Charles Clavadetscher]

Hello

> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Aislan Luiz Wendling
> Sent: Mittwoch, 20. Juli 2016 19:17
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Array value from table as parameter
>
> Hi,
>
> I need to pass an array as a parameter in a function, but it is a part of a 
> trigger that get a "new" value.
>
> I've tested it like 
>
> select function_x(1,55,array[['x'],['y']]) 
> or
> select function_x(1,55,array[['x','y']]) 
>
> and it worked.
>
> But if I use 
>
> select function_x(1,55,new.situations)
>
> it doesn't work, as the value comes like this: {"x","y"}
> I couldn't find a function that converts {} values back to [] without treat 
> it as a string and use replace, what I think that is
not the ideal solution because it may can't satisfy more complex arrays.

Would it help to simply cast the argument to TEXT[]?

select function_x(1,55,(new.situations)::TEXT[]);

I am not sure if the extra parenthesis are necessary.

Regards
Charles




-- 
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] For storing XML version in our table.

[Charles Clavadetscher]

> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of zubair alam
> Sent: Mittwoch, 20. Juli 2016 09:09
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] For storing XML version in our table.

Hi 
How i can store my xml  data with their version in postgres database table.

You can start reading here for the current version (9.5 at the time of this 
writing):

https://www.postgresql.org/docs/current/static/datatype-xml.html
https://www.postgresql.org/docs/current/static/functions-xml.html

Regards
Charles




-- 
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] Running query without trigger?

[Charles Clavadetscher]

Good morning

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of hamann.w@t-
> online.de
> Sent: Samstag, 9. Juli 2016 08:20
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Running query without trigger?
> 
> 
> Hi,
> 
> a table is associated with a trigger for normal use.
> An admin (someone with ALTER privilege) can disable tthe trigger, run some 
> bulk update, and then re-enable it. This
> means, however, that  normal user activity has to be locked out.
> 
> There are two possible scenarios: the bulk update would not cause trigger 
> activity at all, because of the values and
> columns involved.
> or - the bulk update is followed by another bulk transaction that  is 
> equivalent to trigger invocations per row.
> At least in the first case, running this particular query without triggering 
> the trigger, but normal activity still
> going on, would be really great Is there a way to achieve this?

In general it helps better if you provide the version of PostgreSQL that you 
are using.

If you have a way to identify the bulk update from a record field, e.g. a 
timestamp or something like this, you may use WHEN to exclude them from firing 
the trigger. I am not sure if it is possible to use a condition other than 
using the fields of the old or new record.

https://www.postgresql.org/docs/current/static/sql-createtrigger.html

Regards
Charles

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



-- 
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] How SQL SELECT * statement works in Postgres?

[Charles Clavadetscher]

On 06/05/2016 11:16 AM, Sachin Srivastava wrote:

Dear Team,

Kindly inform to me How PostgreSQL Processes SQL Statements internally?

How SQL SELECT * statement works in Postgres?
<http://stackoverflow.com/questions/10927381/how-sql-select-statement-works-in-oracle>


I guess this is a good start:

https://www.postgresql.org/docs/9.5/static/overview.html

Regards,
Charles




Regards,
SSR


--
Charles Clavadetscher
Swiss PostgreSQL Users Group

http://www.swisspug.org
http://www.pgday.ch

+--+
|     __  ___  |
|  /)/  \/   \ Swiss PGDay 2016|
| ( / ___\) 24.06.2016 - HSR CH-Rapperswil |
|  \(/ o)  ( o)   )  http://www.pgday.ch   |
|   \_  (_  )   \ ) _/ |
| \  /\_/\)/ PostgreSQL 1996-2016  |
|  \/  20 Years of Success   |
|   _|  |  |
|   \|_/   |
|  |
+--+


--
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] Ascii Elephant for text based protocols - Final function proposal

[Charles Clavadetscher]

Hello again

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Dienstag, 17. Mai 2016 14:50
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final 
> function proposal
> 
> Hello all
> 
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Karsten
> > Hilbert
> > Sent: Dienstag, 17. Mai 2016 09:23
> > To: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final
> >
> > On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote:
> >
> > > A question to the naming. I find pg_logo() also a good name, but is
> > > the prefix pg_* not reserved for system functions? Of course I could
> > > use the name I want, but was wondering if there is a policy or a
> > > best practice in this area.
> >
> > pg_logo would only be suitable if it got blessing from "higher up".
> 
> Well. This question will be addressed when the body of the function is 
> complete. Now I have a proposal which is in
> the attachment.

In my function there was a problem with casting of values to real instead of 
numeric to compute the number of spaces required on the
right side of a string to be centered. In some specific string lengths the 
rounding was not correct.
Sorry. This should now be ok.

In the attachment the corrected version.

Bye
Charles

--
-- Display the PostgreSQL logo as ASCII art with
-- an optional frame and optional text.
--
CREATE OR REPLACE FUNCTION pg_logo(p_frame BOOLEAN DEFAULT false,
   p_text TEXT[] DEFAULT NULL,
   p_position TEXT DEFAULT 'bottom',
   p_align TEXT DEFAULT 'center',
   p_valign TEXT DEFAULT 'center')
RETURNS SETOF TEXT
AS $$
DECLARE
  v_pic TEXT[] := ARRAY[
  '    __  ___  ',
  ' /)/  \/   \ ',
  '( / ___\)',
  ' \(/ o)  ( o)   )',
  '  \_  (_  )   \ ) _/ ',
  '\  /\_/\)/   ',
  ' \/',
  '  _|  |  ',
  '  \|_/   '];
  v_pic_width INTEGER := coalesce((SELECT max(length(x))
   FROM unnest(v_pic) x),0);
  v_pic_height INTEGER := array_length(v_pic,1);
  -- Get the longest text available or zero if none.
  v_max_text_width INTEGER := coalesce((SELECT max(length(x))
FROM unnest(p_text) x),0);
  v_text_height INTEGER := coalesce(array_length(p_text,1),0);
  -- Compute total width including a space if text is on the right.
  -- This value does not include the frame (if requested).
  v_tot_width INTEGER := CASE WHEN p_position = 'bottom' THEN
greatest(v_max_text_width,v_pic_width)
  ELSE v_pic_width+v_max_text_width+1
 END;
  v_pic_line TEXT;
  v_line_count INTEGER; -- Used for vertical alignment of text
BEGIN
  IF v_text_height > 8 THEN
  END IF;
  -- Check positioning and alignments. Fall back to default if
  -- values are not allowed.
  IF lower(coalesce(p_position,'')) NOT IN ('bottom','right') THEN
p_position := 'bottom';
p_position := lower(p_position);
  END IF;
  IF lower(coalesce(p_align,'')) NOT IN ('left','center','right') THEN
p_align := 'center';
p_align := lower(p_align);
  END IF;
  IF lower(coalesce(p_valign,'')) NOT IN ('top','center','bottom') THEN
p_valign := 'center';
p_valign := lower(p_position);
  END IF;
  -- Add top frame line.
  IF p_frame THEN
RETURN QUERY SELECT '+-'||repeat('-',v_tot_width)||'-+';
  END IF;
  -- Reset counter for vertical alignment of right positioned text.
  CASE WHEN p_valign = 'top' THEN v_line_count := -1; -- It looks better like 
this.
   WHEN p_valign = 'bottom' THEN v_line_count := v_text_height-v_pic_height;
   ELSE v_line_count := (v_text_height-v_pic_height)/2;
   IF v_line_count = 0 THEN v_line_count := -1; -- Correct for case when 
number
   END IF;  -- of text lines is 8
  END CASE;
  FOREACH v_pic_line IN ARRAY v_pic
  LOOP
CASE WHEN p_position = 'bottom' THEN
  CASE WHEN p_align = 'left' THEN
 RETURN QUERY SELECT CASE
   WHEN p_frame THEN '| '||
 v_pic_line||
 repeat(' 
',v_tot_width-length(v_pic_line))||
  

Re: [GENERAL] Ascii Elephant for text based protocols - Final function proposal

[Charles Clavadetscher]

Hello all

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Karsten Hilbert
> Sent: Dienstag, 17. Mai 2016 09:23
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final
> 
> On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote:
> 
> > A question to the naming. I find pg_logo() also a good name, but is
> > the prefix pg_* not reserved for system functions? Of course I could
> > use the name I want, but was wondering if there is a policy or a best
> > practice in this area.
> 
> pg_logo would only be suitable if it got blessing from "higher up".

Well. This question will be addressed when the body of the function is 
complete. Now I have a proposal which is in the attachment.

The function accepts a set of arguments, all having default values:

p_frame BOOLEAN DEFAULT false: values true or false.
p_text TEXT[] DEFAULT NULL: Free text as array of text. Each element of the 
array is a line of text. Maximum of 8 lines. What is
above is ignored.
p_position TEXT DEFAULT 'bottom': position of the text in relation to the pic. 
bottom or right.
p_align TEXT DEFAULT 'center': Alignment of pic AND text, when p_position is 
bottom, only of the text otherwise. Values: left,
center, right.
p_valign TEXT DEFAULT 'center': Vertical alignement of the text. Only applies 
if p_position is right. Values: top, center, bottom.

Below I add a set of sample outputs. Feel free to test it and all feedbacks are 
as usual very much appreciated.

Regards,
Charles

Sample outputs:

No arguments (all defaults):
select pg_logo();
pg_logo
---
     __  ___
  /)/  \/   \
 ( / ___\)
  \(/ o)  ( o)   )
   \_  (_  )   \ ) _/
 \  /\_/\)/
  \/ 
   _|  |
   \|_/
(9 rows)

Add frame:
select pg_logo(true);
  pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\) |
 |  \(/ o)  ( o)   ) |
 |   \_  (_  )   \ ) _/  |
 | \  /\_/\)/|
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Add text using default positioning and alignments:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch']);
 pg_logo
-
 +-+
 |    __  ___  |
 | /)/  \/   \ |
 |( / ___\)|
 | \(/ o)  ( o)   )|
 |  \_  (_  )   \ ) _/ |
 |\  /\_/\)/   |
 | \/|
 |  _|  |  |
 |  \|_/   |
 | |
 |  Swiss PGDay 2016   |
 | 24.06.2016 - HSR Rapperswil |
 | Switzerland |
 | http://www.pgday.ch |
 | |
 +-+
(17 rows)

Position text on the right hand side of the pic:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch'],'right');
pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\)  Swiss PGDay 2016   |
 |  \(/ o)  ( o)   ) 24.06.2016 - HSR Rapperswil |
 |   \_  (_  )   \ ) _/  Switzerland |
 | \  /\_/\)/http://www.pgday.ch |
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Change text alignment to left:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch'],'right','left');
pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\) Swiss PGDay 2016|
 |  \(/ o)  ( o)   ) 24.06.2016 - HSR Rapperswil |
 |   \_  (_  )   \ ) _/  Switzerland |
 | \  /\_/\)/http://www.pgday.ch |
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Change ver

Re: [GENERAL] Fast way to delete big table?

[Charles Clavadetscher]

Hello

> The reason I dont have condition when selecting is it's faster than having.
> Because my aim is to go through every row, find values of few columns and 
> append them to a summary table. Then
> delete the row from the table. So find the rows on the fly is fine for me.
> 
> I have tried to reduce the number of rows to be processed, even I process 1 
> row in production machine, it takes 24
> hours to finish.

This is quite weird... It there any other process locking the record or the 
table?
Bye
Charles

> 
> Regards,
> Haiming
> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/Fast-way-to-delete-big-table-tp5903787p5903917.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



-- 
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] Ascii Elephant for text based protocols - Final

[Charles Clavadetscher]

Hello

On 05/16/2016 10:50 PM, Michael Paquier wrote:

On Tue, May 17, 2016 at 5:13 AM, Karsten Hilbert
 wrote:

 select pg_logo();

seems like a good idea to me :-)


If you propose a patch with a function that returns a setof text, I am
sure it would get some attention. Though I think that you should
remove the mention of the 20th anniversary.



Thank you for the feedbacks.

Good idea. I will write a function. The most basic would be:

CREATE OR REPLACE FUNCTION pg_logo()
RETURNS SETOF TEXT
AS $$
BEGIN
  RETURN QUERY SELECT '    __  ___  '::TEXT;
  RETURN QUERY SELECT ' /)/  \/   \ '::TEXT;
  RETURN QUERY SELECT '( / ___\)'::TEXT;
  RETURN QUERY SELECT ' \(/ o)  ( o)   )'::TEXT;
  RETURN QUERY SELECT '  \_  (_  )   \ ) _/ '::TEXT;
  RETURN QUERY SELECT '\  /\_/\)/   '::TEXT;
  RETURN QUERY SELECT ' \/'::TEXT;
  RETURN QUERY SELECT '  _|  |  '::TEXT;
  RETURN QUERY SELECT '  \|_/   '::TEXT;
  RETURN;
END;
$$ LANGUAGE plpgsql;

charles@charles.[local]=# select pg_logo();
pg_logo
---
     __  ___
  /)/  \/   \
 ( / ___\)
  \(/ o)  ( o)   )
   \_  (_  )   \ ) _/
 \  /\_/\)/
  \/ 
   _|  |
   \|_/
(9 rows)

But I'd like to add arguments so that it is possible to add a frame and 
a text as TEXT[]. Additional arguments for the text would be the 
position (bottom or right) and the alignments (top, bottom, left, right, 
center). All arguments should have default values so that calling the 
function without arguments delivers the logo as in the above basic function.


A question to the naming. I find pg_logo() also a good name, but is the 
prefix pg_* not reserved for system functions? Of course I could use the 
name I want, but was wondering if there is a policy or a best practice 
in this area.


And what schema would be appropriate? I could also create the function 
without defining a schema and let users decide where they want to put it.


An alternative for the name could be logo(), in analogy to version().

Bye
Charles


--
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] Connections - Postgres 9.2

[Charles Clavadetscher]

Hello

> On 16.05.2016, at 18:32, Francisco Olarte  wrote:
> 
> Hi Lucas
> 
>> On Mon, May 16, 2016 at 4:10 AM, Lucas Possamai  wrote:
>> 
>> Those IDLE connections, might be because the user/application didn't commit 
>> the transaction?
> 
> ​IIRC Those would be 'Idle in transaction' ( which are normally bad if 
> numerous, unless your app has a reason for them, as they are the ones which 
> can block things ). Plain 'Idle' are normally connections between 
> transactions, totally normal if you use poolers, or if your app keeps 
> connection opens while it does other things ( like preparing for a 
> transaction ).
> 

There really is a state 'Idle in transaction'? Good to learn.
Thank you and bye
Charles

> Francisco Olarte.
> 
>  
> 

Re: [GENERAL] Connections - Postgres 9.2

[Charles Clavadetscher]

Hello

> On 16.05.2016, at 04:10, Lucas Possamai  wrote:
> 
> Hi guys,
> 
> 
> 
> Those IDLE connections, might be because the user/application didn't commit 
> the transaction?

I think that idle means that a client is connected but is doing nothing. 
Possibly It includes terminated processes without a commit, since they do 
nothing, but I am not sure that you can assume that all connections are 
uncommitted transactions.

Bye
Charles


-- 
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] Ascii Elephant for text based protocols - Final

[Charles Clavadetscher]

Hi all

OK. I must come to an end with this task.

This is what I find a decent final version and I plan to use it in my 
SwissPUG signature until the end of the year.


++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ ) _/   |
| \  /\_/\)/ |
|  \/  |
|   _|  ||
|   \|_/ |
||
|  PostgreSQL 1996-2016  |
|  20 years of success   |
++

It suggests that the trunk is bent behind, like while feeding and I 
assume that associations with phallic symbols doesn't apply anymore.


Thank you to all that provided feedbacks, suggestions and compliments.

SQL version by Melvin Davidson:

CREATE TABLE elephant
(row_num integer NOT NULL,
 row_dat varchar(30) NOT NULL,
 CONSTRAINT elephant_pk PRIMARY KEY (row_num)
);

INSERT INTO elephant
(row_num, row_dat)
VALUES
( 1,'++'),
( 2,'|     __  ___|'),
( 3,'|  /)/  \/   \   |'),
( 4,'| ( / ___\)  |'),
( 5,'|  \(/ o)  ( o)   )  |'),
( 6,'|   \_  (_  )   \ ) _/   |'),
( 7,'| \  /\_/\)/ |'),
( 8,'|  \/  |'),
( 9,'|   _|  ||'),
(10,'|   \|_/ |'),
(11,'||'),
(12,'|  PostgreSQL 1996-2016  |'),
(13,'|  20 Years of success   |'),
(14,'++');

SELECT row_dat FROM elephant ORDER BY row_num;

Regards
Charles


--
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] Ascii Elephant for text based protocols

[Charles Clavadetscher]

I think I found a better representation for the end of the trunk:

++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ ) _/   |
| \  /\_/\)/ |
|  \/  |
||  ||
||_/ |
||
|  PostgreSQL 1996-2016  |
|  20 years of success   |
++

And in Melvin's SQL:

CREATE TABLE elephant
(row_num integer NOT NULL,
 row_dat varchar(30) NOT NULL,
 CONSTRAINT elephant_pk PRIMARY KEY (row_num)
);

INSERT INTO elephant
(row_num, row_dat)
VALUES
( 1,'++'),
( 2,'|     __  ___|'),
( 3,'|  /)/  \/   \   |'),
( 4,'| ( / ___\)  |'),
( 5,'|  \(/ o)  ( o)   )  |'),
( 6,'|   \_  (_  )   \ ) _/   |'),
( 7,'| \  /\_/\)/ |'),
( 8,'|  \/  |'),
( 9,'||  ||'),
(10,'||_/ |'),
(11,'||'),
(12,'|  PostgreSQL 1996-2016  |'),
(13,'|  20 Years of success   |'),
(14,'++');

SELECT row_dat FROM elephant ORDER BY row_num;

Bye
Charles

Annex: Versions

Original:

    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ )  /
\  /\_/\)_/
 \/  //|  |\\
 v |  | v
   \__/

V2 (feedback David Bennett):

    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/<--
\  /\_/\)/  <--
 \/   <--
   |  |
   \__/

V3 (feedback Tom Lane):
    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/
\  /\_/\)/
 \/ 
   |  |
   |/\| <--
Or
    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/
\  /\_/\)/
 \/ 
   |  |
   |__| <--

V4 (myself)

    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/
\  /\_/\)/
 \/ 
   |  |
   |_/ <--



--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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] Build postgresql

[Charles Clavadetscher]

Hello

You can find instructions here:

http://www.postgresql.org/docs/current/static/install-windows.html

Bye
Charles

On 05/16/2016 10:29 AM, Roman Khalupa wrote:

Hello postgresql team!
I have question about building postgresql. And here it is: how to build
postgresql statically on windows to get static libs?

Thanks


--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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] View not allowing to drop column (Bug or Feature enhancement )

[Charles Clavadetscher]

Hello

Please post your answers to the list.


You need to drop the view before recreating it. Then it works. If
you changed the access to the view with grants or revokes, you also
neet to recreate them. They are dropped with the view.


Sorry to say but If we need to drop and replace then what is use of
"Create OR Replace " syntax?


I am not sure about the concrete rationale behind it, but the 
replacement of a view is useful when you make changes in the way how you 
select data from different tables without changing the structure of the 
record returned by the view. In our company views are used a.o. as 
interfaces to applications. They may change during development, but 
later on they become stable. If the design of the tables behind the view 
changes you may need to change the body of the view leaving the 
interface the same. In this way you can optimize performance in the 
database without forcing application developers to make changes to their 
code.


Besides, dropping and creating a view costs nothing in terms of performance.


If its not a bug and a limitation kindly guide me towards any
documentation where it is mentioned.


http://www.postgresql.org/docs/current/static/sql-createview.html

CREATE OR REPLACE VIEW is similar, but if a view of the same name
already exists, it is replaced. The new query must generate the same
columns that were generated by the existing view query (that is, the
same column names in the same order and with the same data types),
but it may add additional columns to the end of the list. The
calculations giving rise to the output columns may be completely
different.


If this is the limitation. Is community is planning update this or add
this feature soon?


Unfortunately I have no answer to that, but somebody else may.
Regards
Charles




--

Thanks and Regards,
Sachin Kotwal


--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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] Fast way to delete big table?

[Charles Clavadetscher]

. In this case,
you should not read, print, retransmit, store or act in reliance on this
e-mail or any attachments, and should destroy all copies of them. This
e-mail and any attachments are confidential and may contain privileged
information and/or copyright material of Redflex or third parties. You
should only retransmit, distribute or commercialise the material if you
are authorised to do so. This notice should not be removed.



--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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] View not allowing to drop column (Bug or Feature enhancement )

[Charles Clavadetscher]

Hello

On 05/16/2016 08:49 AM, Shrikant Bhende wrote:

Hi all,

While working on the view I came across an unusual behaviour of the view,
PostgreSQL do not allows to drop a column from the view, whereas same
pattern of Create and Replace view works while adding a column.

Please find below test for the same.

*
*
*Version info *
*===*
postgres=# select version();
  version
--
  PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)
\d+ orgdata
 Table "public.orgdata"
  Column  | Type  | Modifiers | Storage  | Stats target
| Description
-+---+---+--+--+-
  id  | integer| not null  | plain|  |
  name| character varying(20) | not null  | extended |  |
  address | character varying(20) |   | extended |  |
  age | integer| not null  | plain|  |
  salary  | numeric(10,0)|   | main |  |
Indexes:
 "orgdata_pkey" PRIMARY KEY, btree (id)
Triggers:
 example_trigger AFTER INSERT ON orgdata FOR EACH ROW EXECUTE
PROCEDURE auditlogfunc()

*Creating view *
postgres=# create or replace view vi1  as
select id , name from orgdata ;
CREATE VIEW

*Alter command do not have any option to drop column*
postgres=# alter view vi1
ALTER COLUMN  OWNER TO  RENAME TO SET SCHEMA


Neither there is an option to add a column.


*
*
*To add columns it will work.*
**
postgres=# create or replace view vi1 as
postgres-# select id, name, age from orgdata ;
CREATE VIEW


What you are doing is actually a CREATE OR REPLACE VIEW and not a ALTER 
VIEW. In this case it is allowed to add columns *at the end of the list* 
(see below).



*
*
*While trying to drop a column by replacing view definition from view it
throws an error saying cannot drop column from view.*
*=*
postgres=# create or replace view vi1 as select
id , name from orgdata ;
*ERROR:  cannot drop columns from view*


You need to drop the view before recreating it. Then it works. If you 
changed the access to the view with grants or revokes, you also neet to 
recreate them. They are dropped with the view.



If its not a bug and a limitation kindly guide me towards any
documentation where it is mentioned.


http://www.postgresql.org/docs/current/static/sql-createview.html

CREATE OR REPLACE VIEW is similar, but if a view of the same name 
already exists, it is replaced. The new query must generate the same 
columns that were generated by the existing view query (that is, the 
same column names in the same order and with the same data types), but 
it may add additional columns to the end of the list. The calculations 
giving rise to the output columns may be completely different.


Regards,
Charles



Thanks.
--
Shrikant Bhende
+91-9975543712


--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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] Ascii Elephant for text based protocols

[Charles Clavadetscher]

Hello Melvin

Thank you for your code.

With the current design (not yet necessarily the final one):

CREATE TABLE elephant
(row_num integer NOT NULL,
 row_dat varchar(30) NOT NULL,
 CONSTRAINT elephant_pk PRIMARY KEY (row_num)
);

INSERT INTO elephant
(row_num, row_dat)
VALUES
( 1,'++'),
( 2,'|     __  ___|'),
( 3,'|  /)/  \/   \   |'),
( 4,'| ( / ___\)  |'),
( 5,'|  \(/ o)  ( o)   )  |'),
( 6,'|   \_  (_  )   \ ) _/   |'),
( 7,'| \  /\_/\)/ |'),
( 8,'|  \/  |'),
( 9,'||  ||'),
(10,'||/\||'),
(11,'||'),
(12,'|  PostgreSQL 1996-2016  |'),
(13,'|  20 Years of success   |'),
(14,'++');

SELECT row_dat FROM elephant ORDER BY row_num;

Bye
Charles

On 05/16/2016 05:56 AM, Melvin Davidson wrote:

To all, thanks for the concept. The following based on original  design
submission, might help in tweaking:

CREATE TABLE elephant
(row_num integer NOT NULL,
  row_dat varchar(30) NOT NULL,
  CONSTRAINT elephant_pk PRIMARY KEY (row_num)
);

INSERT INTO elephant
(row_num, row_dat)
VALUES
( 1,'++'),
( 2,'|     __  ___|'),
( 3,'|  /)/  \/   \   |'),
( 4,'| ( / ___\)  |'),
( 5,'|  \(/ o)  ( o)   )  |'),
( 6,'|   \_  (_  )   \ )  /   |'),
( 7,'| \  /\_/\)_/|'),
( 8,'|  \/  //|  |\\  |'),
( 9,'|  v |  | v  |'),
(10,'|\__/|'),
(11,'||'),
(12,'|  PostgreSQL 1996-2016  |'),
(13,'|  20 Years of success   |'),
(14,'++');

SELECT row_dat FROM elephant ORDER BY row_num;


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


--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


--
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] Ascii Elephant for text based protocols

[Charles Clavadetscher]

Hello Tom

Thanks for your feedback.

On 05/16/2016 05:31 AM, Tom Lane wrote:

Charles Clavadetscher <clavadetsc...@swisspug.org> writes:

On 05/16/2016 02:51 AM, dandl wrote:

* the trunk is (how shall I put this?) somewhat phallic.



Mmh... This could apply to any elephant picture. I don't think that this
needs a change, but let's see if there are more feedbacks.


I'd say the problem is at the end: elephant trunks don't curve that
direction.


True. I let myself lead more by the original picture than by zoology.


Instead of


|  v |  | v  |
|\__/|


maybe


|  v |  | v  |
||__||


or even


|  v |  | v  |
||/\||


The second variant seems better. Now it looks like this.

    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/
\  /\_/\)/
 \/ 
   |  |
   |/\|

Have a good day.
Charles



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] Ascii Elephant for text based protocols

[Charles Clavadetscher]

Hello

On 05/16/2016 02:51 AM, dandl wrote:

I agree, but I'm hoping someone with design skills (not me!) will step in to
tweak it somewhat.

Comparing it side by side with the one on the web site
* the right ear lobe is too low
* the tusks point wrong


Well. It's ASCII and there are some limitations. Here a second version, 
trying to address your two points.


New:
    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ ) _/
\  /\_/\)/
 \/ 
   |  |
   \__/
Old:
    __  ___
 /)/  \/   \
( / ___\)
 \(/ o)  ( o)   )
  \_  (_  )   \ )  /
\  /\_/\)_/
 \/  //|  |\\
 v |  | v
   \__/


* the trunk is (how shall I put this?) somewhat phallic.


Mmh... This could apply to any elephant picture. I don't think that this 
needs a change, but let's see if there are more feedbacks.


Thx
Charles


A worthy goal, not quite there yet.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Peter J. Holzer
Sent: Monday, 16 May 2016 9:11 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Ascii Elephant for text based protocols

On 2016-05-15 14:02:56 +0200, Charles Clavadetscher wrote:

++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ )  /   |
| \  /\_/\)_/|
|  \/  //|  |\\  |
|  v |  | v  |
|\__/|
||
|  PostgreSQL 1996-2016  |
|  20 Years of success   |
++


Nice.

 hp

--
_  | Peter J. Holzer| I want to forget all about both belts and
|_|_) || suspenders; instead, I want to buy pants
| |   | h...@hjp.at | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/







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

[GENERAL] Ascii Elephant for text based protocols

[Charles Clavadetscher]

Hello

We have a long week-end this days and so I tried to create an elephant 
using ASCII characters that could be used for mail signatures or 
wherever you prefer not to use a graphical interface. I tried to find 
something ready on the internet but was unable to spot anything useful 
and small enough for this purpose.


You can see the result below, enriched with this year's anniversary 
information. Now, since I am not really a gifted designer I was 
wondering if somebody has any ideas on how to improve the result.


If the community finds the draft ok and the idea meaningful, it could 
even be added to the list of available logos?


Enjoy and thanks for any feedback.
Charles


Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org

++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ )  /   |
| \  /\_/\)_/|
|  \/  //|  |\\  |
|  v |  | v  |
|\__/|
||
|  PostgreSQL 1996-2016  |
|  20 Years of success   |
++


--
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] Thoughts on "Love Your Database"

[Charles Clavadetscher]

Good morning

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guyren Howe
> Sent: Mittwoch, 4. Mai 2016 06:11
> To: pgsql-general 
> Subject: [GENERAL] Thoughts on "Love Your Database"
> 
> I've long been frustrated with how most web developers I meet have no idea 
> how to use an SQL database properly. I
> think I'm going to write a book called Love Your Database, aimed at web 
> developers, that explains how to make their
> apps better by leveraging the power of SQL in general, and Postgres in 
> particular.
> 
> I'm thinking of a section on features of SQL most folks don't know about 
> (CTEs are *way* to hell at the top of that
> list, but also EXCEPT/INTERSECT and window functions), but much of the book 
> would be about how to do things server
> side. Benchmarks showing how much faster this can be, but mostly techniques — 
> stored procedures/triggers/rules,
> views.
> 
> I asked a colleague about the advice I often hear stated but seldom 
> justified, that one shouldn't put business rules
> in the database. He offered that server-side code can be hard to debug.
> 
> I'm sure many here would love to see such a book published, maybe some talks 
> on the topic given.
> 
> 
> What might I cover that I haven't mentioned? What are the usual objections to 
> server-side code and how can they be
> met? When *are* they justified and what should the criteria be to put code in 
> Postgres? Any other thoughts? Any
> other websites or books on the topic I might consult?

If you have a complex design or if the processes require the modification of 
various tables within a transaction you may probably prefer to expose functions 
as the application interface. Advantages of this approach:

- Hide complexity: You don't need to explain all the details, dependencies and 
implications to all web developers. Just make sure that your documentation is 
up-to-date for those who want to learn about it.
- Transactions are controlled by the database: You may have doubts if 
application developers do handle this correctly.
- Minimize the impact on application development: If changes to requirements 
force changes in the database, these would be transparent to the application. 
Even if the interface changes, that may mean only an additional argument to a 
function.
- Security: You can grant execute on (security definer) functions instead of 
granting privileges for each object. The latter can become quite complex.
- Separation of concerns: Application developers don't need to (but can if they 
want) learn SQL. They should focus instead on the presentation layer, which at 
the end is what customers see and sells.

Bye
Charles

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



-- 
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] Why are data files stored in /var/lib

[Charles Clavadetscher]

Hello Manuel

> -Original Message-
> From: Manuel Gómez [mailto:tar...@gmail.com]
> Sent: Samstag, 30. April 2016 05:45
> To: Charles Clavadetscher <clavadetsc...@swisspug.org>
> Cc: Postgres General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Why are data files stored in /var/lib
> 
> On Fri, Apr 29, 2016 at 11:00 PM, Charles Clavadetscher
> <clavadetsc...@swisspug.org> wrote:
> > I had a discussion yesterday with some friends, who are sysadmins about the 
> > location of database files. In a
> default installation
> > from a distribution (apt-get install) PostgreSQL creates a cluster unter 
> > /var/lib/. According to my colleagues
> /var/lib should not
> > contain data that is supposed to last over time.
> 
> Your sysadmin friends should read the excellent
> http://www.pathname.com/fhs/pub/fhs-2.3.html#VARLIBVARIABLESTATEINFORMATION
> Filesystem Hierarchy Standard
> 
> Chapter 5. The /var Hierarchy
> 
> Purpose
> 
> /var contains variable data files. This includes spool directories and
> files, administrative and logging data, and transient and temporary
> files.
> 
> Some portions of /var are not shareable between different systems. For
> instance, /var/log, /var/lock, and /var/run. Other portions may be
> shared, notably /var/mail, /var/cache/man, /var/cache/fonts, and
> /var/spool/news.
> 
> /var is specified here in order to make it possible to mount /usr
> read-only. Everything that once went into /usr that is written to
> during system operation (as opposed to installation and software
> maintenance) must be in /var.
> 
> […]
> 
> /var/lib : Variable state information
> 
> Purpose
> 
> This hierarchy holds state information pertaining to an application or
> the system. State information is data that programs modify while they
> run, and that pertains to one specific host. Users must never need to
> modify files in /var/lib to configure a package's operation.
> 
> State information is generally used to preserve the condition of an
> application (or a group of inter-related applications) between
> invocations and between different instances of the same application.
> State information should generally remain valid after a reboot, should
> not be logging output, and should not be spooled data.

Thank you a lot. That (and the additional info in the link) explains it very 
well.
Bye
Charles




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

[GENERAL] Why are data files stored in /var/lib

[Charles Clavadetscher]

Hello

I use version 9.5 on Linux Mint 17.3.

I had a discussion yesterday with some friends, who are sysadmins about the 
location of database files. In a default installation
from a distribution (apt-get install) PostgreSQL creates a cluster unter 
/var/lib/. According to my colleagues /var/lib should not
contain data that is supposed to last over time. I am aware that the location 
can be modified to suit the needs of sysadmins as
described under these links.

http://www.postgresql.org/docs/current/static/storage-file-layout.html
http://www.postgresql.org/docs/current/static/runtime-config-file-locations.html

In the first link there is a line stating that "A common location for PGDATA is 
/var/lib/pgsql/data". My question is if there is a
specific reason for choosing /var/lib as the default for database file 
locations, some kind of best practices or alike. I assume
that there have been discussions on the topic?

Thank you and enjoy the weekend.
Charles




-- 
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] 9.5: tsvector problem

[Charles Clavadetscher]

Hello Johann

There are two to_tsvector functions:

charles@charles.[local]=# \df to_tsvector
 List of functions
   Schema   |Name | Result data type | Argument data types | 
Type

+-+--+-+
 pg_catalog | to_tsvector | tsvector | regconfig, text | normal
 pg_catalog | to_tsvector | tsvector | text| normal
(2 rows)

I think that you may try casting the result of coalesce() to TEXT.

Bye
Charles

On 04/26/2016 03:25 PM, Johann Spies wrote:

I have never seen this problem before.  It occurred while trying to
import a dump (done by 9.5 client of a 9.4 database) also.

Table definition:

-
CREATE TABLE source.annual
(
   filename text,
   gzipfile text,
   id serial NOT NULL,
   tsv tsvector,
   ut character varying(19),
   xml xml,
   processed boolean,
   CONSTRAINT annual_pkey PRIMARY KEY (id)
)
WITH (
   OIDS=FALSE
);
CREATE INDEX xml_tsv_idx
   ON source.annual
   USING gin
   (tsv);


CREATE TRIGGER tsvectorupdate_source_xml
   BEFORE INSERT OR UPDATE
   ON source.annual
   FOR EACH ROW
   EXECUTE PROCEDURE source.update_xml_tsv();


And the trigger function:

CREATE OR REPLACE FUNCTION source.update_xml_tsv()
   RETURNS trigger AS
$BODY$

begin

new.tsv :=  to_tsvector('english', coalesce(new.xml,''));

return new;

end
$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;


When I try to insert a value into this  table, I get:
==
ERROR:  function to_tsvector(unknown, xml) does not exist
LINE 1: SELECT to_tsvector('english', coalesce(new.xml))
^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:  SELECT to_tsvector('english', coalesce(new.xml))
CONTEXT:  PL/pgSQL function source.update_xml_tsv() line 5 at assignment

** Error **

ERROR: function to_tsvector(unknown, xml) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might
need to add explicit type casts.
Context: PL/pgSQL function source.update_xml_tsv() line 5 at assignment
=

But I can do

select to_tsvector('English', 'This is a problem')

without a problem.

What is causing this?

Regards
Johann

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


--

Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


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

[GENERAL] max_stack_depth problem though query is substantially smaller

[Charles Clavadetscher]

Hello

We have a process in R which reads statistical raw data from a table and 
computes time series values from them.
The time series values are in a hstore field with the date as the key and the 
value as the value.
The process writes the computed value into a temporary table and locks the 
corresponding row(s) of the target table for update.
Finally the row(s) are updated if they already exist or inserted if they do not 
exist.

This process runs nightly and processes many GB of data without generating any 
error. Normally these are low frequency time series
(monthly or quarterly data).

Now we have a daily time serie with about 46'000 key/value pairs. In near 
future this will increase to 86'000 including data from
previous years.

When R processes the daily time serie we get a stack size exceeded error, 
followed by the hint to increase the max_stack_depth. My
colleague, who wrote the R function and performed the test read the docs and 
increased, according to ulimit -s the max_stack_depth
to 7MB.

Here the details of OS and PG:
OS: osx 10.10.5
PG: 9.3.3

ulimit -s = 8192

The resize did work as *show max_stack_depth;" has shown. After this change, 
however, the query states the same error as before,
just with the new limit of 7 MB.

The query itself was written to a file in order to verify its size. The size 
turned out to be 1.7MB, i.e. even below the
conservative default limit of 2 MB, yet alone substantially below 7 MB.

Apart from the fact that we could consider using a different strategy to store 
time series, we would like to understand what is
causing the problem.

Here the query as it looks like in the R code:
sql_query_data <- sprintf("BEGIN;
   CREATE TEMPORARY TABLE ts_updates(ts_key varchar, 
ts_data hstore, ts_frequency integer) ON COMMIT DROP;
   INSERT INTO ts_updates(ts_key, ts_data) VALUES %s;
   LOCK TABLE %s.timeseries_main IN EXCLUSIVE MODE;

   UPDATE %s.timeseries_main
   SET ts_data = ts_updates.ts_data
   FROM ts_updates
   WHERE ts_updates.ts_key = %s.timeseries_main.ts_key;

   INSERT INTO %s.timeseries_main
   SELECT ts_updates.ts_key, ts_updates.ts_data, 
ts_updates.ts_frequency
   FROM ts_updates
   LEFT OUTER JOIN %s.timeseries_main ON 
(%s.timeseries_main.ts_key = ts_updates.ts_key)
   WHERE %s.timeseries_main.ts_key IS NULL;
   COMMIT;",
   values, schema, schema, schema, schema, schema, 
schema, schema)

And here is how it looks like at the end:

INSERT INTO ts_updates(ts_key, ts_data, ts_frequency) VALUES 
('somekey',hstore('1900-01-01','-0.395131869823009')||

hstore('1900-01-02','-0.595131869823009')||

hstore('1900-01-03','-0.395131869823009')||
[...] 
46'000 times

hstore('1900-01-04','-0.395131869823009'),NULL);

The computer where my colleague made the test is local. There are no other 
concurrent users.
We thank you for hints on what the problem may be and/or how to investigate it 
further.

Please reply to all, as my colleague is not yet subscribed to the mailing list.

Regards,
Charles and Matthias




-- 
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] CONCAT returns null

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer
> Sent: Sonntag, 28. Februar 2016 11:30
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] CONCAT returns null
> 
> Sterpu Victor  wrote:
> 
> > Hello
> >
> > I have this concat:
> > CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, '-MM-DD'), f2.nrfo, TO_CHAR
> > (fd7.validfrom, '-MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))
> > that works fine but when I change to this(I added a ' with '):
> > ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, '-MM-DD'), ' with 
> > ',
> > f2.nrfo, TO_CHAR(fd7.validfrom, '-MM-DD'), DATE(fd5.validto)-DATE
> > (fd1.validfrom))) AS temp,
> > then concat returns NULL.
> > Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is
> > still NULL.
> >
> > Thank you
> 
> NULL concat with a value returns NULL. You can avoid that using
> COALESCE(value, ''), that returns the value, or, if the value NULL, ''.

That was my first thought, too, but:

db=> select concat(null,'sssdf',null,'dfg',NULL);
  concat
--
 sssdfdfg
(1 row)

Bye Charles




-- 
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] CONCAT returns null

[Charles Clavadetscher]

Hello again

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Sonntag, 28. Februar 2016 11:24
> To: 'Sterpu Victor' <vic...@caido.ro>; 'PostgreSQL General' 
> <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] CONCAT returns null
> 
> Hello
> 
> Just a short question to that.
> 
> > CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, '-MM-DD'), f2.nrfo, 
> > TO_CHAR(fd7.validfrom, '-MM-DD'),
> > DATE(fd5.validto)-DATE(fd1.validfrom))
> > that works fine but when I change to this(I added a ' with '):
> > ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, '-MM-DD'), ' with 
> > ', f2.nrfo, TO_CHAR(fd7.validfrom,
> '-
> > MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))) AS temp,
> > then concat returns NULL.
> > Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is 
> > still NULL.
> 
> Do you get NULL also if you run the query without the call to array_agg(), 
> i.e. for all entries in your table?

Forget it. Should not depend on that. If you have any null entry it will be 
added in the array as NULL.
Bye
Charles




-- 
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] CONCAT returns null

[Charles Clavadetscher]

Hello

Just a short question to that.

> CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, '-MM-DD'), f2.nrfo, 
> TO_CHAR(fd7.validfrom, '-MM-DD'),
> DATE(fd5.validto)-DATE(fd1.validfrom))
> that works fine but when I change to this(I added a ' with '):
> ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, '-MM-DD'), ' with ', 
> f2.nrfo, TO_CHAR(fd7.validfrom, '-
> MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom))) AS temp,
> then concat returns NULL.
> Why? I tried to add ' with '::VARCHAR and ' with '::TEXT but the result is 
> still NULL.

Do you get NULL also if you run the query without the call to array_agg(), i.e. 
for all entries in your table?

Bye
Charles




-- 
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: missing FROM-clause entry for table

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of bigkev
> Sent: Mittwoch, 10. Februar 2016 08:11
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] ERROR: missing FROM-clause entry for table
> 
> I am receiving this error for the query pasted below.
> Is the LEFT JOIN on the table not enough?
> What needs to happen here?
> I am guess something to do with derived tables
> 
> http://pastie.org/10715876

It would help to know for which table the clause entry is missing. I guess that 
the order of the joins is not correct:

left join generate_series(c.start_time, c.end_time, '2 weeks'::interval) 
f(fortnight) ON g.day=f.fortnight
LEFT JOIN call_schedule c on extract(dow from c.start_time) = extract(dow from 
g.day)  AND f.fortnight IS NOT NULL AND g.day BETWEEN
c.start_time AND c.end_time

In the first line you use c, but this is declared on the following line.

Bye
Charles

> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750.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



-- 
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: check constraint - PostgreSQL 9.2

[Charles Clavadetscher]

Hello Vitaly

> -Original Message-
> From: Vitaly Burovoy [mailto:vitaly.buro...@gmail.com]
> Sent: Montag, 25. Januar 2016 14:25
> To: Christophe Pettus <x...@thebuild.com>; clavadetsc...@swisspug.org
> Cc: Postgres General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2
> 
> On 1/24/16, Christophe Pettus <x...@thebuild.com> wrote:
> >
> > On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher
> > <clavadetsc...@swisspug.org> wrote:
> >
> >> What is the point of having a check constraint that is not checked?
> >
> > Well, it *is* checked going into the future; it's just not checked at the
> > time the constraint is added.  Ultimately, you do want to fix the data, but
> > this makes it a two-step process, and reduces the time the table is locked
> > against access.
> 
> NOT VALID constraint checks new and updated rows, and gives an extra
> time to fix current data and be sure there will be no new rows that
> violates the check constraint during and after the fixing process.
> 
> N.B.: Prior 9.4 it does *NOT* reduce the time the table is locked
> because VALIDATE CONSTRAINT requires ACCESS EXCLUSIVE[1] and uses
> seqscan for check table's rows.
> 
> P.S. Lucas, If you have not received answers, you can find all of them
> as a thread by the link:
> http://www.postgresql.org/message-id/flat/CAE_gQfXTns1FR5Fx9wxpo1oZYwat639ua-gAqWZyNg201HCU=q...@mail.gmail.com
> 
> P.P.S.:  Christophe, Charles! Please, use "Relpy to all" to be sure
> the sender gets your answers even if he haven't subscribed to the
> mailing list.

Oops. Honestly I did not think of that. I will keep that in mind in the future.
Thank you for the hint.

Charles

> 
> [1]http://www.postgresql.org/docs/9.2/static/sql-altertable.html
> --
> Best regards,
> Vitaly Burovoy



-- 
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: check constraint - PostgreSQL 9.2

[Charles Clavadetscher]

Hello

Althought both options are technically correct, I guess that the first one is 
the only reasonable one. What is the point of having a
check constraint that is not checked? If all fields in the check constraint 
must not be null there must be a reason for it. Possibly
the "wrong" data is useless anyway (some test data that was not deleted) or the 
constraint only applies from a certain point in time
because something in the system built on top of it changed. In the latter case, 
since the data has a time stamp you may extend the
constraints to include the point in time from which it must apply.

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Christophe Pettus
> Sent: Montag, 25. Januar 2016 05:18
> To: Postgres General 
> Subject: Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2
> 
> 
> On Jan 24, 2016, at 8:12 PM, "drum.lu...@gmail.com"  
> wrote:
> 
> > How can I solve the problem? How can I get the command successfully be done?
> 
> Two options:
> 
> 1. Fix the data.
> 
> 2. Use the NOT VALID option on ALTER TABLE ... ADD constraint, which allows 
> the addition of a constraint without
> actually checking its validity.
> 
> --
> -- Christophe Pettus
>x...@thebuild.com
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] Giving error for function

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sachin Srivastava
> Sent: Dienstag, 12. Januar 2016 08:40
> To: pgsql-general@postgresql.org >> PG-General Mailing List 
> 
> Subject: [GENERAL] Giving error for function
> 
> Hi,
> 
> I am getting the below error while I am running the below function 
> "add_po_doc_hist", for "c_company" cursor, please
> suggest what is wrong with code.
> 
> 
> 
> ERROR:  relation "c_company" does not exist

Probably you need to schema qualify the table name.

> 
> CONTEXT:  compilation of PL/pgSQL function "add_po_doc_hist" near line 11
> 
> ** Error **
> 
> 
> 
> ERROR: relation "c_company" does not exist
> 
> SQL state: 42P01
> 
> Context: compilation of PL/pgSQL function "add_po_doc_hist" near line 11
> 
> 
> 
> 
> 
>  
> ---
> --
> 
> -- Function: add_po_doc_hist(bigint)
> 
> 
> 
> -- DROP FUNCTION add_po_doc_hist(bigint);
> 
> 
> 
> CREATE OR REPLACE FUNCTION add_po_doc_hist(subscriberid bigint)
> 
>   RETURNS void AS
> 
> $BODY$
> 
> DECLARE
> 
> 
> 
> --Select All Companies for the subscriber entered
> 
> c_company CURSOR FOR
> 
> SELECT company_id
> 
> from PSM_COMPANY_PROFILE
> 
> where is_BUYER = 1
> 
>   and subscriber_id=subscriberID;
> 
> 
> 
> v_company c_company%ROWTYPE;
> 
> counter bigint :=1;
> 
> 
> 
> BEGIN
> 
> 
> 
> open c_company;
> 
> loop
> 
> fetch c_company into v_company;
> 
> IF NOT FOUND THEN EXIT; END IF; -- apply on 
> c_company
> 
> 
> 
> --insert in PDOC_CHANGE_HIST_HEADER
> 
> insert into PDOC_CHANGE_HIST_HEADER
> 
> (SUBSCRIBER_ID, COMPANY_ID, 
> DOCUMENT_ID,
> 
>   DESCRIPTION, COMMENTS,
> 
>   CREATION_DATE, CREATION_USER, UPDATE_DATE, UPDATE_USER )
> 
> values(subscriberID, v_company.company_id, 
> 15197,
> 
> 'Buyer PO History', '',
> 
> LOCALTIMESTAMP, 'AppAdmin', LOCALTIMESTAMP, 'AppAdmin' );
> 
> 
> 
> --First Row insert in PDOC_CHANGE_HIST_DETAIL
> 
> insert into PDOC_CHANGE_HIST_DETAIL
> 
> (SUBSCRIBER_ID, COMPANY_ID, 
> DOCUMENT_ID, ROW_NUMBER,
> 
>   TABLE_NAME, TABLE_COLUMN_NAME, FIELD_ID,
> 
>   CREATION_DATE, CREATION_USER, UPDATE_DATE, UPDATE_USER )
> 
> values(subscriberID, v_company.company_id, 
> 15197, 1,
> 
> 'PPO_MASTER_HEADER', 'SUPPLIER_CONTACT_ID', 15385,
> 
> LOCALTIMESTAMP, 'AppAdmin', LOCALTIMESTAMP, 'AppAdmin' );
> 
> 
> 
> --Second Row insert in PDOC_CHANGE_HIST_DETAIL
> 
> insert into PDOC_CHANGE_HIST_DETAIL
> 
> (SUBSCRIBER_ID, COMPANY_ID, 
> DOCUMENT_ID, ROW_NUMBER,
> 
>   TABLE_NAME, TABLE_COLUMN_NAME, FIELD_ID,
> 
>   CREATION_DATE, CREATION_USER, UPDATE_DATE, UPDATE_USER )
> 
> values(subscriberID, v_company.company_id, 
> 15197, 2,
> 
> 'PPO_MASTER_HEADER', 'STATUS', 15192,
> 
> LOCALTIMESTAMP, 'AppAdmin', LOCALTIMESTAMP, 'AppAdmin' );
> 
> 
> 
>--Third Row insert in PDOC_CHANGE_HIST_DETAIL
> 
> insert into PDOC_CHANGE_HIST_DETAIL
> 
> (SUBSCRIBER_ID, COMPANY_ID, 
> DOCUMENT_ID, ROW_NUMBER,
> 
>   TABLE_NAME, TABLE_COLUMN_NAME, FIELD_ID,
> 
>   CREATION_DATE, CREATION_USER, UPDATE_DATE, UPDATE_USER )
> 
> values(subscriberID, v_company.company_id, 
> 15197, 3,
> 
> 'PPO_MASTER_HEADER', 'APPROVAL_PERSON_ID', 20883,
> 
> LOCALTIMESTAMP, 'AppAdmin', LOCALTIMESTAMP, 'AppAdmin' );
> 
> 
> 
> counter := counter +1;
> 
> /*if it is more than 1000 record then commit 
> and reset the counter value*/
> 
> if( counter = 1000) then
> 
> commit;
> 
> counter :=1;
> 
> end if;
> 
> end loop;
> 
> commit;
> 
> close c_company;
> 
> 
> 
> END;
> 
> $BODY$
> 
>   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
> 
>   COST 100;
> 
> ALTER FUNCTION add_po_doc_hist(bigint)
> 
>   OWNER TO postgres;
> 
> 
> 
> 
> 
> 
> 
> --
> 
> 
> 
> 
> Regards,
> 
> Sachin




-- 
Sent via pgsql-general mailing list 

Re: [GENERAL] Slow Query - PostgreSQL 9.2

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Saulo Merlo
> Sent: Montag, 11. Januar 2016 08:12
> To: Vitaly Burovoy 
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> 
> gorgs.inode_segments:
> 
> 
>   -- Table: gorfs.inode_segments
> 
>   -- DROP TABLE gorfs.inode_segments;
> 
>   CREATE TABLE gorfs.inode_segments
>   (
> st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs 
> to. alongside segment_index, it forms
> the table's primary key to ensure uniqueness per relevant scope
> segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's 
> column description for further details.
> The meaning of this column varies based on the host inode type:...
> st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for 
> directory inode segments (objects in
> the directory)
> full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical 
> path for quick lookups. Meaningful
> only for directory inode segments (objects in the directory)
> segment_data "bytea", -- Actual data segment. Meaningful only for 
> S_IFLNK and S_IFREG
> CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
> CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
> REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
> REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
> CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" 
> IS NULL OR "st_ino"::bigint <>
> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
>   )
>   WITH (
> OIDS=FALSE
>   );

There is no field st_ctime.

> 
>   S_IFSOCK:   0:   no data to store, no records here
>   S_IFLNK:1:   contains the link target (see columns comments for 
> details).
>   S_IFREG:0+:  actual data segments, up to 64MB each  (see columns 
> comments for details)
>   S_IFBLK:0:   no data to store, no records here
>   S_IFDIR:0+:  one record per object name in the directory
>   S_IFCHR:0:   no data to store, no records here
>   S_IFIFO:0:   no data to store, no records here
>   ';
>   -- Index: gorfs.ix_inode_segments_climb_tree
> 
>   -- DROP INDEX gorfs.ix_inode_segments_climb_tree;
> 
>   CREATE INDEX ix_inode_segments_climb_tree
> ON gorfs.inode_segments
> USING btree
> ("segment_index" COLLATE pg_catalog."default", "st_ino_target");
> 
>   -- Index: gorfs.ix_inode_segments_filter_by_subtree
> 
>   -- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;
> 
>   CREATE INDEX ix_inode_segments_filter_by_subtree
> ON gorfs.inode_segments
> USING btree
> ("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)
> WHERE "full_path" IS NOT NULL;
>   COMMENT ON INDEX gorfs.ix_inode_segments_filter_by_subtree
> IS 'Allows looking for left-anchored paths (either regex or LIKE).
>   WARNING: as of 9.2 the index is not used when the comparison term is a 
> non deterministic function (STABLE or
> VOLATILE).
>   See http://www.postgresql.org/message-id/5451d6c4.7040...@vuole.me
>   ';
> 
>   -- Index: gorfs.ix_inode_segments_full_path_resolution
> 
>   -- DROP INDEX gorfs.ix_inode_segments_full_path_resolution;
> 
>   CREATE INDEX ix_inode_segments_full_path_resolution
> ON gorfs.inode_segments
> USING btree
> ("st_ino", "full_path" COLLATE pg_catalog."default");
> 
>   -- Index: gorfs.ix_inode_segments_gsdi_pk
> 
>   -- DROP INDEX gorfs.ix_inode_segments_gsdi_pk;
> 
>   CREATE INDEX ix_inode_segments_gsdi_pk
> ON gorfs.inode_segments
> USING btree
> (("st_ino"::"text") COLLATE pg_catalog."default", 
> ("segment_index"::"text") COLLATE pg_catalog."default");
> 
>   -- Index: gorfs.ix_inode_segments_ja_files_lookup
> 
>   -- DROP INDEX gorfs.ix_inode_segments_ja_files_lookup;
> 
>   CREATE INDEX ix_inode_segments_ja_files_lookup
> ON gorfs.inode_segments
> USING btree
> ((
>   CASE
>   WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN 
> "upper"("regexp_replace"("full_path"::"text",
> '.*\.'::"text", ''::"text", 'g'::"text"))
>   ELSE NULL::"text"
>   END) COLLATE pg_catalog."default")
> WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
> 
>   -- Index: gorfs.ix_inode_segments_notes_clientids
> 
>   -- DROP INDEX gorfs.ix_inode_segments_notes_clientids;
> 
>   CREATE INDEX 

Re: [GENERAL] Function error

[Charles Clavadetscher]

Hello

If I understand you correctly you have two functions create_catexp_ss_1 and 
create_catexp_ss_2 that you then call from create_catexp_master.
If so then you probably need to change the call to them:

> -- Exposure for single supplier without category filtering
> create_catexp_ss_1;
> 
> -- Exposure for single supplier with category filtering
> create_catexp_ss_2;

Should be:

PERFORM create_catexp_ss_1();
PERFORM create_catexp_ss_2();

If necessary with according parameters and assuming that you have no values 
returned that you need.

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sachin Srivastava
> Sent: Freitag, 8. Januar 2016 08:24
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Function error
> 
> Dear Concern,
> 
> 
> 
> I am creating below function “create_catexp_ss_master()” and getting error as 
> below, I have already created
> dependent function firstly successfully (“create_catexp_ss_1” and 
> “create_catexp_ss_2”) but still getting error,
> please suggest why?
> 
> 
> 
> ERROR:  syntax error at or near "create_catexp_ss_1"
> 
> LINE 38:  create_catexp_ss_1;
> 
>   ^
> 
> ** Error **
> 
> 
> 
> ERROR: syntax error at or near "create_catexp_ss_1"
> 
> SQL state: 42601
> 
> Character: 1104
> 
> 
> 
> 
> 
> 
> 
> -- Function: create_catexp_ss_master()
> 
> 
> 
> -- DROP FUNCTION create_catexp_ss_master();
> 
> 
> 
> CREATE OR REPLACE FUNCTION create_catexp_ss_master()
> 
>   RETURNS void AS
> 
> $BODY$
> 
> DECLARE
> 
> 
> 
> -- Build snapshot tables for catalog itme exposure.
> 
> 
> 
> -- Versions:
> 
> -- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13
> 
> -- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen and 
> from the Code. 05/23/13
> 
> --
> 
> 
> 
> v_count_before bigint;
> 
> v_count_after bigint;
> 
> v_start_time timestamp;
> 
> v_err_msg varchar(1000);
> 
> v_set_name varchar(10);
> 
> 
> 
> BEGIN
> 
> v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;
> 
> SELECT LOCALTIMESTAMP INTO v_start_time ;
> 
> 
> 
> if v_set_name='A' then
> 
> SELECT count(1) INTO v_count_before FROM 
> pcat_exp_supp_buyer_ss_a;
> 
> else
> 
> SELECT count(1) INTO v_count_before FROM 
> pcat_exp_supp_buyer_ss_b;
> 
> end if;
> 
> 
> 
> -- Remove old data.
> 
> EXECUTE 'truncate table pcat_exp_supp_buyer_ss_'||v_set_name;
> 
> EXECUTE 'truncate table 
> pcat_exp_supp_cat_buyer_ss_'||v_set_name;
> 
> 
> 
> -- Exposure for single supplier without category filtering
> 
> create_catexp_ss_1;
> 
> 
> 
> -- Exposure for single supplier with category filtering
> 
> create_catexp_ss_2;
> 
> 
> 
> if v_set_name='A' then
> 
> SELECT count(1) INTO v_count_after FROM 
> pcat_exp_supp_buyer_ss_a;
> 
> else
> 
> SELECT count(1) INTO v_count_after FROM 
> pcat_exp_supp_buyer_ss_b;
> 
> end if;
> 
> 
> 
> -- Log
> 
> create_ss_log('Catalog Exposure', v_start_time, 
> 'pcat_exp_supp_buyer_ss_'||v_set_name,
> 
> v_count_before, v_count_after, null);
> 
> 
> 
> exception-- log error
> 
> when others then
> 
> v_err_msg := SQLERRM;
> 
> create_ss_log('Catalog Exposure - Error', v_start_time, 
> 'pcat_exp_supp_buyer_ss_'||v_set_name,
> 
> v_count_before, 
> v_count_after, v_err_msg);
> 
> 
> 
> END;
> 
> $BODY$
> 
>   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
> 
>   COST 100;
> 
> ALTER FUNCTION create_catexp_ss_master()
> 
>   OWNER TO postgres;
> 
> 
> 
> 
> 
> 
> 
> Regards,
> SS
> 




-- 
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] How do I implement a .XSD in Postgres?

[Charles Clavadetscher]

Hello

 

XML is an extensible object description language and XSD is not an XML 
derivative, but an implementation of the XML extensibility as a replacement for 
DTD. It is finally nothing else than an XML file following the validation 
schema of itself (  
http://www.w3.org/2001/XMLSchema). Being XML you can load it into an XML field 
in PostgreSQL just like any other XML document (e.g. xml, wsdl, xslt, soap, 
etc.).

 

You can find a possible way on how to do it here: 
http://www.schmiedewerkstatt.ch/wiki/index.php/PostgreSQL:_Writing_text,_xml_content_into_a_field

 

Bye

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G. Johnston
Sent: Freitag, 1. Januar 2016 19:02
To: ERR ORR 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I implement a .XSD in Postgres?

 

On Fri, Jan 1, 2016 at 3:07 AM, ERR ORR  > wrote:

Hi everybody,

I need to import some DB schemas which are defined in .XSD (a XML derivative) 
format.

I've googled about this but have found no satisfactory answer. Perhaps I just 
don't know what exactly to ask Google.

So please:

- What tool can I use in order to import .XSD schema definitions into 
Postgresql 9.4.5?

- If there is no tool, can you please direct me to a document at least hinting 
at how to import a .XSD schema into a DB?

Thanks and happy new year to all.

 

​So, you are basically looking for an XSD to SQL converter, potentially with 
knowledge of PostgreSQL implementation specifics​.  No clue - though like 
Adrian said the source of the XSD file would ideally have meaningful knowledge 
of how to actually use it.

 

David J.

 

Re: [GENERAL] Strange syntax with select

[Charles Clavadetscher]

Hello

 

I am not in clear what your use case is, but you may have a look at that:

 

http://www.depesz.com/2013/02/25/variables-in-sql-what-how-when/

 

The bottom line is that in a psql interactive session you cannot really set a 
variable as the result of a select statement (or at least I did not fine a 
way). Instead, depending on what you actually want to achieve, you may use a 
workaround storing a statement or part of it in a variable.

 

Here an example:

db=> create table test (id int);

CREATE TABLE

db => insert into test select generate_series(1,10);

INSERT 0 10

db => \set testvar 'sum(id) from test'

db => select :testvar;

sum

-

 55

(1 row)

 

Bye

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Edson F. Lidorio
Sent: Freitag, 25. Dezember 2015 14:23
To: pgsql-general@postgresql.org
Subject: [GENERAL] Strange syntax with select

 

Hello,
I excuse my ignorance with SQL and my English.
I wonder if these procedures are correct or is it a bug?
I'm trying to put the result of a select within the v_saldo_ini variable, 
except I realized that the postgresql created a table with v_saldo_ini variable.

See the steps below:

CREATE TABLE contas
(
   vlr_saldo_inicial numeric(14,2)) ;

select sum(vlr_saldo_inicial) as saldo_ini
into v_saldo_ini
from contas;

--postgresql created v_saldo_ini table

select * from v_saldo_ini;
 saldo_ini 
---
  2.00
(1 record)


--if I executat = ro select below, returns the table v_saldo_ini already exists

select sum(vlr_saldo_inicial) as saldo_ini
 into v_saldo_ini
from contas;




ERROR: relation "v_saldo_ini" already exists


I'm using version below PostgreSQL.

PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 
4.9.2, 64-bit

--
Edson 



 

Re: [GENERAL] Transfer db from one port to another

[Charles Clavadetscher]

Hello Killian

>> I want to transfer my db with three schemas from port 5432 to port 5532 to 
>> use
>> within the bitnami stack. I have used pgAdmin to create a backup.sql and when
>> using pgAdmin to restore the .sql to port 5532 I get the following error
>
>Try it with plain pg_dump.
>
>pg_dump -h localhost -p 5432 -Fc  > dump.sql
>
>pg_restore -h localhost -p 5532 dump.sql
>
>I tried this, but nothing appears to happen when entering the commands. 
>Attached is a screenshot of the shell window - what am I doing wrong? 

This should be done from an OS shell, not from psql.

Bye
Charles




(untestet, please read *before* the documentation)


I think, this should work. No idea what's wrong with pgAdmin, not using
that.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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




-- 
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] Transfer db from one port to another

[Charles Clavadetscher]

From: Killian Driscoll [mailto:killiandrisc...@gmail.com] 
Sent: Mittwoch, 23. Dezember 2015 11:02
To: Charles Clavadetscher <clavadetsc...@swisspug.org>
Cc: Andreas Kretschmer <akretsch...@spamfence.net>; pgsql-general 
<pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Transfer db from one port to another

 

On 23 December 2015 at 10:58, Charles Clavadetscher <clavadetsc...@swisspug.org 
<mailto:clavadetsc...@swisspug.org> > wrote:

Hello Killian

>> I want to transfer my db with three schemas from port 5432 to port 5532 to 
>> use
>> within the bitnami stack. I have used pgAdmin to create a backup.sql and when
>> using pgAdmin to restore the .sql to port 5532 I get the following error
>
>Try it with plain pg_dump.
>
>pg_dump -h localhost -p 5432 -Fc  > dump.sql
>
>pg_restore -h localhost -p 5532 dump.sql
>
>I tried this, but nothing appears to happen when entering the commands. 
>Attached is a screenshot of the shell window - what am I doing wrong?

This should be done from an OS shell, not from psql.

Do you mean Windows command prompt? 

 

  Yes

 


Bye
Charles





(untestet, please read *before* the documentation)


I think, this should work. No idea what's wrong with pgAdmin, not using
that.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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



 

Re: [GENERAL] Transfer db from one port to another

[Charles Clavadetscher]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 11:19
To: John R Pierce 
Cc: pgsql-general 
Subject: Re: [GENERAL] Transfer db from one port to another

 

On 23 December 2015 at 11:07, John R Pierce  > wrote:

On 12/23/2015 1:40 AM, Killian Driscoll wrote:

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc  > dump.sql

pg_restore -h localhost -p 5532 dump.sql

 

I tried this, but nothing appears to happen when entering the commands. 
Attached is a screenshot of the shell window - what am I doing wrong? 



those are system shell commands, not psql sql commands.  catch-22, in the 
windows environment, postgresql's command tools probably aren't in the path, so 
to execute the above commands try this...

start -> run ->  CMD 

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName> path "c:\Program Files\PostgreSQL\9.4\bin";%path%
C:\Users\YourName> pg_dump -Fc -p 5432  | pg_restore -p 5532

Thanks. When I do this I get an error: could not find a "pg_dump" to execute - 
I've used the path "C:\Program Files\PostgreSQL\9.3\bin";%path% which appears 
to be correct 

 

  You may try calling the app without setting the path first or check the 
location browsing the file system:

  C:\Program Files\PostgreSQL\9.3\bin\ pg_dump -Fc -p 5432 

 

  Don’t forget to replace  with the database that you want to dump.



if your postgres is installed somewhere else, replace "c:\Program 
Files\PostgreSQL\9.4\bin" in the PATH command with its actual location \bin  









-- 
john r pierce, recycling bits in santa cruz

 

Re: [GENERAL] Transfer db from one port to another

[Charles Clavadetscher]

We had that already upthread.

Did you set the path to the bin dir of PostgreSQL as of previous posts?

 

Regards

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 15:14
To: Adrian Klaver 
Cc: John R Pierce ; pgsql-general 

Subject: Re: [GENERAL] Transfer db from one port to another

 

On 23 December 2015 at 14:56, Adrian Klaver  > wrote:

On 12/23/2015 03:43 AM, Killian Driscoll wrote:

On 23 December 2015 at 11:36, John R Pierce  
 >> wrote:

On 12/23/2015 2:25 AM, Killian Driscoll wrote:

Sorry, forgot to add: once I get the warning that the Pg_dump
can't be found there is then a password prompt; I tried the db
password and the pc password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project"
failed: FATAL:  p
assword authentication failed for user "killian"


note that databases don't have passwords, database USERS have
passwords.  'killian' probably doesn't have a database user, and
since you didn't specify a user, it defaulted to your system
username (expecting that user to have been created in postgres, and
wanting that probably non-existant postgres users passsword)

so, ok, try the command with -U postgres, as *
*

*pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore
-U postgres -p 5532*

OK - I did the dir and it shows that the dump and restore.exe are there,
but running the above gives the below errors

09/06/2014  08:35   381,952 pg_dump.exe

09/06/2014  08:35   180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project |
pg_restore -U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to
execute

pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] could not write to output file: Invalid argument



Try breaking the above down into two steps:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

 

Doing this step I get response

could not find a "pg_dump" to execute 


pg_restore -U postgres -p 5532 irll_project.out

 



if/when it prompts for a password, thats the password of the
'postgres' database user, as configured in the postgres servers.

note it will prompt for the password a couple times,  once for
postgres on port 5432, and again for postgres on port 5532, at least
if both database services are configured to require passwords for
local connections.


--
john r pierce, recycling bits in santa cruz



 

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