[GENERAL] Prevent roles not having admin option from granting themselves to other roles

[Charles Clavadetscher]

Good morning

I am investigating the authorization possiblities of PostgreSQL and I
stumbled on a case, whose rationale I could not find in any resource online.
For that reason I post my question here.

First of all let me state that the software is acting accordingly to the
documentation. The passage I'd like to ask about is in the notes to the
GRANT command and it goes es follows:

"If WITH ADMIN OPTION is specified, the member can in turn grant membership
in the role to others, and revoke membership in the role as well. Without
the admin option, ordinary users cannot do that. A role is not considered to
hold WITH ADMIN OPTION on itself, but it may grant or revoke membership in
itself from a database session where the session user matches the role.
Database superusers can grant or revoke membership in any role to anyone.
Roles having CREATEROLE privilege can grant or revoke membership in any role
that is not a superuser."

An ordinary user is therefore able to grant itself to other users in a
database session where the session user matches the role. Actually the
normal case, if am not completely wrong.
My question is: What is the point of preventing a role to grant specific
privileges it has, but allow it to grant all privileges at once, granting
itself as a role?

Now consider this case to illustrate what I mean:

Create a test environment:
charles@admin.localhost=# CREATE ROLE user1 LOGIN PASSWORD 'xxx';
charles@admin.localhost=# CREATE ROLE user2 LOGIN PASSWORD 'xxx';
charles@admin.localhost=# CREATE DATABASE test;

Login to new DB as superuser:
charles@admin.localhost=# \c test
You are now connected to database "test" as user "charles".

Change access settings and create an object:
charles@test.localhost=# REVOKE ALL ON DATABASE test FROM PUBLIC;
charles@test.localhost=# CREATE SCHEMA test_schema;
charles@test.localhost=# CREATE TABLE test_schema.test_table (id INTEGER);
charles@test.localhost=# \dt test_schema.*
 List of relations
   Schema|Name| Type  |  Owner
-++---+-
 test_schema | test_table | table | charles

Grant access to the object to user1:
charles@test.localhost=# GRANT CONNECT ON DATABASE test TO user1;
charles@test.localhost=# GRANT USAGE ON SCHEMA test_schema TO user1;
charles@test.localhost=# GRANT SELECT, INSERT, UPDATE, DELETE ON
test_schema.test_table TO user1;

Login as user1:
charles@test.localhost=# \c - user1
user1@test.localhost=> INSERT INTO test_schema.test_table VALUES (9);
INSERT 0 1
user1@test.localhost=> SELECT * FROM test_schema.test_table;
 id

  9
(1 row)

So far user2 has not received any specific grants from the superuser (or any
database admin for that sake). It cannot connect or do any damage:
user1@test.localhost=> \c - user2
Password for user user2:
FATAL:  permission denied for database "test"
DETAIL:  User does not have CONNECT privilege.

Additionally user1 cannot GRANT the privileges that it has to others:
user1@test.localhost=> GRANT SELECT ON test_schema.test_table TO user2;
WARNING:  no privileges were granted for "test_table"

But user1 can grant itself to user2:
user1@test.localhost=> GRANT user1 TO user2;

At this point user2 can do anything that user1 can do, i.e. connect to the
database and modify data:
user1@test.localhost=> \c - user2
You are now connected to database "test" as user "user2".
user2@test.localhost=> DELETE FROM test_schema.test_table;
DELETE 1

Althought this is the behaviour described in the documentation that means
that any user can potentially grant access to any database it has access to
without the knowledge of the database responsible.

IMHO not giving the ADMIN option of the GRANT statement to a role should
prevent it from granting itself to others, since it makes little sense to
prevent a user from granting individual privileges, but allow it to grant
all of its privileges at once. But there may be very good reasons for this
behaviour which I am not aware of.

I thank you for your explanations and if you have any, for a strategy to
prevent roles to grant themselves to others. In my searches so far, I could
not find any information on that and I was not able to find a strategy
myself.
Thank you and have a good day.
Charles Clavadetscher




-- 
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] Prevent roles not having admin option from granting themselves to other roles

[Charles Clavadetscher]

Hello Adrian

Thank you very much for your response. As a matter of fact I already had a
look into the NOINHERIT attribute and its consequences. This partially
solves the problem but makes the usage of user accounts in applications more
complex.

If I wanted to create a group for accessing the database, i.e. grant CONNECT
to the group and grant the group to the users that should be able to access
that specific database, then users having the NOINHERIT attribute will not
be able to connect. So I would end up granting CONNECT to each individual
user. The approach would help in terms of disabling the ability for a role
granting itself to another to pass on the CONNECT privilege (for the same
reason that it does not work with groups). But legitimate users in the
database would not profit from the grouping ability or, better said, the
application would need to switch between containing roles in order to
perform their tasks. This requires the application to know in which roles to
"set" into in order to access specific objects.

Maybe I still don't see the point, but this approach seems to me to be more
complicated (and error prone), while it would be much simpler to only allow
superusers to grant roles to roles.

My original question was also about the reason why ordinary users should be
able to grant all their privileges to others granting them their role. This
sounds more of less like giving out your password. The latter we may not be
able to prevent, but the first could have a technical solution.

Regards
Charles

>See here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html
>
>In particular the section on INHERIT and in NOTES this:
>
>"The INHERIT attribute is the default for reasons of backwards
>compatibility: in prior releases of PostgreSQL, users always had access to
all privileges of groups they were members of. However, NOINHERIT provides a
closer match to the semantics specified in the SQL standard."
>
>Also see here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-set-role.html
>
>for what SET ROLE does under INHERIT versus NOINHERIT




-- 
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] Prevent roles not having admin option from granting themselves to other roles

[Charles Clavadetscher]

Hello

Well I was thinking a litte more on this. Basically I think that it could
end up with a small set of "rules":

- Organize privileges in groups (nologin, inherit) is a way that
independently of how many levels of indirection you have each definite scope
or application has a single group to "set role" into.
- Users have noinherit, thus forcing applications they use to log into the
database to perform a set role to the group designed for that application.
- Users have individually CONNECT privilege to the database(s) they need to
connect to.
- Users have no other privileges granted directly (apart from connect to
database) to avoid switching between group and user.

Well at least where I am it is getting late and I guess I can use some fresh
air.

Regards
Charles

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
Clavadetscher
Sent: Donnerstag, 11. Juni 2015 16:28
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Prevent roles not having admin option from granting
themselves to other roles

Hello Adrian

Thank you very much for your response. As a matter of fact I already had a
look into the NOINHERIT attribute and its consequences. This partially
solves the problem but makes the usage of user accounts in applications more
complex.

If I wanted to create a group for accessing the database, i.e. grant CONNECT
to the group and grant the group to the users that should be able to access
that specific database, then users having the NOINHERIT attribute will not
be able to connect. So I would end up granting CONNECT to each individual
user. The approach would help in terms of disabling the ability for a role
granting itself to another to pass on the CONNECT privilege (for the same
reason that it does not work with groups). But legitimate users in the
database would not profit from the grouping ability or, better said, the
application would need to switch between containing roles in order to
perform their tasks. This requires the application to know in which roles to
"set" into in order to access specific objects.

Maybe I still don't see the point, but this approach seems to me to be more
complicated (and error prone), while it would be much simpler to only allow
superusers to grant roles to roles.

My original question was also about the reason why ordinary users should be
able to grant all their privileges to others granting them their role. This
sounds more of less like giving out your password. The latter we may not be
able to prevent, but the first could have a technical solution.

Regards
Charles

>See here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-createrole.html
>
>In particular the section on INHERIT and in NOTES this:
>
>"The INHERIT attribute is the default for reasons of backwards
>compatibility: in prior releases of PostgreSQL, users always had access 
>to
all privileges of groups they were members of. However, NOINHERIT provides a
closer match to the semantics specified in the SQL standard."
>
>Also see here:
>
>http://www.postgresql.org/docs/9.4/interactive/sql-set-role.html
>
>for what SET ROLE does under INHERIT versus NOINHERIT




--
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] Trying to avoid a simple temporary variable declaration in a pl/pgsql function

[Charles Clavadetscher]

Hello

The solution proposed by Tom works as long as you can make sure that 
your SELECT statement in the function will return a single row with a 
single column of type TEXT:


CREATE TABLE test (id INTEGER, what_goes_here TEXT);
INSERT INTO test values (1,'Text 1');
INSERT INTO test values (2,'Text 2');

CREATE FUNCTION test_func()
RETURNS text
LANGUAGE 'plpgsql'
AS $$
BEGIN
RETURN (SELECT what_goes_here FROM test LIMIT 1);
END;
$$;

SELECT * FROM test_func();
 test_func
---
 Text 1
(1 row)

No need for INTO.

Bye
Charles

On 6/20/2015 17:07, David G. Johnston wrote:
On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane >wrote:


"David G. Johnston" mailto:david.g.johns...@gmail.com>> writes:
> I know this could be written quite easily in sql but was
wondering if it is
> possible in pl/pgsql.

> CREATE FUNCTION test_func()
> RETURNS text
> LANGUAGE 'plpgsql'
> AS $$
> BEGIN
> SELECT 'text_to_return' INTO ; --with or
without a cast
> RETURN ;
> END;
> $$;

> The goal is to return the value of text_to_return without
declaring an
> explicit variable to name in the INTO clause.

INTO requires a declared variable as target.

However, I'm wondering why you don't just use "RETURN expression"
if this is all that will be in the function.


​ The use of SELECT is required and will likely have a CTE and a set 
of SQL CASE expressions as part of it.

​
It isn't a problem to declare it myself but I thought I had read about 
there being an implicit variable name that could be used instead.  I 
guess I mis-remembered...


​Thanks for the quick response.

David J.






--
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] INSERT a number in a column based on other columns OLD INSERTs

[Charles Clavadetscher]

Hello

I just made a short test with the code provided. As Bill mentioned the 
moment when the trigger is fired is essential.
I made a test with both before (worked) and after (did not work because 
the row was already inserted and the returned new row is ignored).


The assignment (= or :=) does not seem to play a role, but the correct 
version is as mentioned :=


Bye
Charles

On 6/20/2015 21:37, Bill Moran wrote:

On Sat, 20 Jun 2015 10:44:21 -0700 (MST)
litu16  wrote:


In PostgreSQL I have this table... (there is a primary key in the most left
side "timestamp02" which is not shown in this image)

in the table above, all columns are entered via querrys, except the
"time_index" which I would like to be filled automatically via a trigger
each time each row is filled.

This is the code to create the same table (without any value) so everyone
could create it using the Postgre SQL query panel.

*CREATE TABLE table_ebscb_spa_log02
(
   pcnum smallint,
   timestamp02 timestamp with time zone NOT NULL DEFAULT now(),
   fn_name character varying,
   "time" time without time zone,
   time_elapse character varying,
   time_type character varying,
   time_index real,
   CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02)
)
WITH (
   OIDS=FALSE
);
ALTER TABLE table_ebscb_spa_log02
   OWNER TO postgres;*

What I would like the trigger to do is:

INSERT a number in the "time_index" column based on the INSERTed values of
the "fn_name" and "time_type" columns in each row.

If both ("fn_name" and "time_type") do a combination (eg. Check Mails -
Start) that doesn't exist in any row before (above), then INSERT 1 in the
"time_index" column,

Elif both ("fn_name" and "time_type") do a combination that does exist in
some row before (above), then INSERT the number following the one
before(above) in the "time_index" column.

(pls look at the example table image, this trigger will produce every red
highlighted square on it)


I have tried so far this to create the function:

CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$
DECLARE
t_ix real;
n int;

BEGIN
IF NEW.time_type = 'Start' THEN
 SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name =
NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1
INTO t_ix;
   GET DIAGNOSTICS n = ROW_COUNT;
 IF (n = 0) THEN
 t_ix = 1;
 ELSE
 t_ix = t_ix + 1;
 END IF;
END IF;
NEW.time_index = t_ix;
return NEW;
END
$$
LANGUAGE plpgsql;


But when I manually insert the values in the table, nothing change (no error
message) time_index column just remain empty, what am I doing wrong???

Please some good PostgreSQL fellow programmer could give me a hand, I really
have come to a death point in this task, I have any more ideas.

Couple things.

First off, you don't show your statement for creating the trigger. This is 
important.
The trigger has to be a BEFORE trigger FOR EACH ROW. Otherwise, the returned 
value
won't do anything. It should read like this:

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_ebscb_spa_log02
  FOR EACH ROW EXECUTE PROCEDURE on_ai_myTable();

If you created it with AFTER INSERT or FOR EACH STATEMENT, then the trigger 
won't
work as desired.

The other thing about assignment being := was already mentioned.





--
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] INSERT a number in a column based on other columns OLD INSERTs

[Charles Clavadetscher]

Hi

The two things have nothing in common. With NEW.time_index = t_ix you 
set the field with a value and with return you return the record (with 
the modified field) to make the insert.
You have to return NEW, because you have a trigger function. The 
function must return a record of the same type as the table the trigger 
has been created for. You can see it as this:


You make an insert into a table that has a trigger implemented in a 
trigger function.

BEFORE it is inserted, the record is passed as NEW to the trigger function.
In the function, in this case, you modify a field of NEW and return the 
modified record.
Now the insert is done as usual using the NEW record returned by the 
trigger function.


Bye
Charles

On 6/21/2015 03:07, litu16 wrote:

Hi, thanks
yes I was using AFTER, but it only works with BEFORE
so finally I got it to work.
thanks to all

Im just still wondering
why here...

*BEGIN
IF NEW.time_type = 'Start' THEN
 SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name =
NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1
INTO t_ix;
   GET DIAGNOSTICS n = ROW_COUNT;
 IF (n = 0) THEN
 t_ix = 1;
 ELSE
 t_ix = t_ix + 1;
 END IF;
END IF;
NEW.time_index = t_ix;
return NEW;
END
$$
LANGUAGE plpgsql;*

I have to put return NEW, instead of NEW.time_index = t_ix

Thanks to all again.



--
View this message in context: 
http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577p5854602.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.






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

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

[Charles Clavadetscher]

Just a final note. If your trigger needs to handle updates or deletes, 
then you may need to use OLD or both (OLD and NEW) and return OLD 
instead of NEW, depending on what you want to achieve. You also may 
return null to avoid the action to be performed, but this only works 
with "before" triggers. I would recommend you to read the documentation 
on trigger functions. It is excellent and clarifies quite a lot how 
things work.


Bye
Charles

On 6/21/2015 16:49, Charles Clavadetscher wrote:

Hi

The two things have nothing in common. With NEW.time_index = t_ix you 
set the field with a value and with return you return the record (with 
the modified field) to make the insert.
You have to return NEW, because you have a trigger function. The 
function must return a record of the same type as the table the 
trigger has been created for. You can see it as this:


You make an insert into a table that has a trigger implemented in a 
trigger function.
BEFORE it is inserted, the record is passed as NEW to the trigger 
function.
In the function, in this case, you modify a field of NEW and return 
the modified record.
Now the insert is done as usual using the NEW record returned by the 
trigger function.


Bye
Charles

On 6/21/2015 03:07, litu16 wrote:

Hi, thanks
yes I was using AFTER, but it only works with BEFORE
so finally I got it to work.
thanks to all

Im just still wondering
why here...

*BEGIN
IF NEW.time_type = 'Start' THEN
 SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name =
NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC 
LIMIT 1

INTO t_ix;
   GET DIAGNOSTICS n = ROW_COUNT;
 IF (n = 0) THEN
 t_ix = 1;
 ELSE
 t_ix = t_ix + 1;
 END IF;
END IF;
NEW.time_index = t_ix;
return NEW;
END
$$
LANGUAGE plpgsql;*

I have to put return NEW, instead of NEW.time_index = t_ix

Thanks to all again.



--
View this message in context: 
http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577p5854602.html

Sent from the PostgreSQL - general mailing list archive at Nabble.com.










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

Re: [GENERAL] create index on a field of udt

[Charles Clavadetscher]

Hello

 

I am not sure it is that simple. Probably you need to create operator classes 
to be used for indexing.

 

http://www.postgresql.org/docs/9.4/static/xtypes.html

 

You are probably better off using the basic data type in your table and using a 
composite index.

 

Bye

Charles

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Montag, 29. Juni 2015 07:51
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] create index on a field of udt

 

On 6/28/2015 10:31 PM, Shujie Shang wrote:

Oh, I didn't explain my question well, actually I want to create an index on an 
udt in a table. 

 

e.g.

create type info as (id int, name text);

creat table test (i info);

I want to run:

create index myindex on test (i.id  )


create table test of info primary key(id);

or, if you want to use your type plus other stuff in the table, I believe its 
something like...

create table test (i info, stuff...) primary key (i.id)
or
create index test(i.id);


watch out for ambiguity if the type names match the table or field name.  see 
http://www.postgresql.org/docs/current/static/rowtypes.html#AEN7836




-- 
john r pierce, recycling bits in santa cruz

Re: [GENERAL] create index on a field of udt

[Charles Clavadetscher]

+1

 

create index on test (((i).id));

ANALYZE

explain select * from test where (i).id = 8909;

   QUERY PLAN

-

Index Scan using test_id_idx on test  (cost=0.43..8.45 rows=1 width=34)

   Index Cond: ((i).id = 8909)

(2 rows)

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jeff Janes
Sent: Montag, 29. Juni 2015 08:42
To: Shujie Shang
Cc: John R Pierce; PostgreSQL mailing lists
Subject: Re: [GENERAL] create index on a field of udt

 

On Sun, Jun 28, 2015 at 10:31 PM, Shujie Shang mailto:ssh...@pivotal.io> > wrote:

Oh, I didn't explain my question well, actually I want to create an index on an 
udt in a table.

 

e.g.

create type info as (id int, name text);

creat table test (i info);

I want to run:

create index myindex on test (i.id  )

 

 

 

It is a matter of finding the correct level of magic parentheses.

 

create index on test (((i).id));

 

The outer layer are always needed for creating indexes.  The middle layer are 
needed because you are indexing an expression, not a column.  And the inner 
layer is needed because, well, that is just how udt works.

 

Cheers,

 

Jeff

Re: [GENERAL] Ubuntu 14.04 LTS install problem

[Charles Clavadetscher]

Hello Urs

I could install PostgreSQL 9.4 on Ubuntu 14.04 without adding repositories 
(actually it was an upgrade and I had to move the data manually from 9.3 to 
9.4). You may want to try it that way?

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Urs Berner
> Sent: Mittwoch, 1. Juli 2015 07:40
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Ubuntu 14.04 LTS install problem
> 
> I installed Ubuntu 14.04 LTS
> apg-get update ... apt-get upgrade ...
> 
> and looked at
> www.postgresql.org/download/linux/ubuntu
> then added apt repository /etc/apt/sources.list.d/pgdg.list
> 
> When I try
> 
> apt-get install postgresql-9.4
> 
> error:
>  depends on postgresql-common (>= 142~) what should not get installed
> ... you have defect packages ...
> 
> any ideas?
> 
> Urs
> 
> 
> 
> 
> 
> 
> --
> 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] Ubuntu 14.04 LTS install problem

[Charles Clavadetscher]

I need to check that when I get home. Suddendly I have the impression that I 
may have added a repository to check 9.4 before it was released.
I will get back at that if nobody else can offer a solution, but it may take 
some time.

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Urs Berner
> Sent: Mittwoch, 1. Juli 2015 11:06
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Ubuntu 14.04 LTS install problem
> 
> Am 01.07.2015 um 09:08 schrieb Charles Clavadetscher:
> > Hello Urs
> >
> > I could install PostgreSQL 9.4 on Ubuntu 14.04 without adding repositories
> (actually it was an upgrade and I had to move the data manually from 9.3 to
> 9.4). You may want to try it that way?
> >
> > Bye
> > Charles
> 
> the Ubuntu-Software Center offers postgresql-9.3, but when trying to install
> I get an error:
> 
> dependencies missing, "postgresql:" but not more hints
> 
> Because it is a totally new computer I have not yet had an older instance of
> postgresql.
> 
> When I try via terminal
> apt-get install postgresql-9.3 there are the same messages about postgresql-
> common (>= 142~) ... defect packages ...
> 
> 
> 
> 
> --
> 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] Download PostgreSQL 9.5 Alpha

[Charles Clavadetscher]

Hello

I also could not find the download on EDB. For Ubuntu 9.5 alpha
is available but I was not able to install it using apt-get because it 
cannot resolve some dependencies. Finally I downloaded and compiled the 
source code. This worked well, but I had to do some additional steps to 
get the cluster up and running. I wrote some notes on


http://www.schmiedewerkstatt.ch/wiki/index.php/PostgreSQL_Compiling_From_Source

In general the problems were:
- Full qualify the executables if you have other instances running on 
your system (in my case 9.4.4). This to make sure that you are using the 
newer versions.
- Set LD_LIBRARY_PATH to the lib dir in your installation directory. The 
same as before but for libraries.


You may prefer to set PATH to the 9.5 lib dir in your current shell 
instead of qualifying the executables.


Hope this helps.

BTW. I am not sure about that, but I guess that if I hadn't had 9.4 
already on the system apt-get would have worked. The error message 
suggested that it did not want to replace some existing files (e.g. 
libpq if I remember well). I assumed that this was intended to avoid a 
working version to be "damaged".


Bye
Charles

On 7/4/2015 02:24, Joshua D. Drake wrote:


On 07/03/2015 04:32 PM, Edson F. Lidorio wrote:


Hello,

PostgreSQL 9.5 Alpha not appear on the downloads list in [1]
Where do I download for Windows?

[1]
http://www.enterprisedb.com/products-services-training/pgdownload#windows



For those in the community who may not know, EnterpriseDB hosts the
Windows versions of PostgreSQL.

Edson,

It does not appear that they have a Alpha download available yet.

Sincerely,

JD



--
Edson








--
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] String match function required like utl_match in oracle

[Charles Clavadetscher]

Or maybe even better:

http://www.postgresql.org/docs/9.4/static/fuzzystrmatch.html

Search for Levenshtein.

Bye
Charles

On 7/4/2015 11:50, Jimit Amin wrote:

Hello,

I want to compare 2 string and want result like how much percentage or
how much part string is compared. Can I know any option like utl_match
in oracle

Jimit Amin




--
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_hba.conf] publish own Python application using PostgreSQL

[Charles Clavadetscher]

Hi

I am not really an expert, but from your description I guess that you 
assume an existing PostgreSQL installation on your customers' server. If 
that is the case you probably won't get around giving instructions to 
your customer and let them do the change. I would not like to install 
applications that change settings in pg_hba.conf on my server. Besides 
you may consider limiting the trust access to the specific user and 
specific database that your application needs to access.


Bye
Charles

On 7/5/2015 12:15, c.bu...@posteo.jp wrote:

I have my own Python application using a PostgreSQL database over
SQLAlchemy.

Currently I pack the application in a deb-file.
After installation (on a fresh system! Ubuntu 14.04.2) it doesn't run
because of some PostgreSQL-settings.
Of course I understand why and I know (a little bit) which settings I
have to do to make it run.

But the point is I don't want to plague my user to do that.

How could this be solved?
How can I release a application using a local PostgreSQL-database.
I am not sure if the modifications I do are correct or elegant (see
below).

These are the modification I have to do to make my application run with
the connetion string "postgres://puser@localhost/FoobarTest".

The settings are about
the /etc/postgresql/9.3/main/pg_hba.conf file.
There I change this line
hostall all 127.0.0.1/32md5
to
hostall all 127.0.0.1/32trust

I have to create a user without a password (beside the admin/postgres),
too.





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

[GENERAL] Row level security - notes and questions

[Charles Clavadetscher]

Hello

I have been testing the new row level security feature of 9.5 and I have
some notes and questions on it.

This is  a simple table for the test, with 2 rows and a user named john, who
is granted access to the table through a group named users.

CREATE TABLE testrls.accounts (
  id integer,
  username text,
  userinfo text
);

INSERT INTO testrls.accounts
VALUES (1,'john','Main accountant'),
   (2,'fred','Practitioner');

CREATE ROLE users NOLOGIN;
CREATE ROLE john LOGIN PASSWORD 'xxx';

GRANT USAGE ON SCHEMA testrls TO users;
GRANT SELECT, INSERT, UPDATE, DELETE ON testrls.accounts TO users;
GRANT users TO john;

My first test is to enable row level security on the table without a policy
in place. According to the documentation this leads to a general deny
access.

ALTER TABLE testrls.accounts ENABLE ROW LEVEL SECURITY;

When user john tries to interact with the table he receives an empty result
set, instead of a policy violation error. There is no policy yet, so this
may be acceptable. I find it however confusing.

john@test=> SELECT * FROM testrls.accounts ;
 id | username | userinfo 
+--+--
(0 rows)

Since the result set is empty UPDATE and DELETE also do nothing.

In the case of an INSERT john receives a policy violation error. Still there
is no policy yet for the table. This seems not consistent with the behaviour
for the other commands.

john@test=> INSERT INTO testrls.accounts VALUES (3,'lucy','Secretary');
ERROR:  new row violates row level security policy for "accounts"

For the next example I created a policy that allows users to read all rows,
but only change those "belonging" to them, identified by the column
username.

CREATE POLICY accounts_policy ON testrls.accounts
FOR ALL
TO users
USING (true)
WITH CHECK (username = SESSION_USER);

john@test=> SELECT * FROM testrls.accounts ;
 id | username |userinfo 
+--+-
  1 | john | Main accountant
  2 | fred | Practitioner
(2 rows)

john@test.localhost=> INSERT INTO testrls.accounts VALUES
(3,'lucy','Secretary');
ERROR:  new row violates row level security policy for "accounts"
john@test=> UPDATE testrls.accounts SET userinfo = 'Whatever' WHERE id = 2;
ERROR:  new row violates row level security policy for "accounts"
john@test=> UPDATE testrls.accounts SET userinfo = 'Whatever' WHERE username
= 'fred';
ERROR:  new row violates row level security policy for "accounts"

Up to this point everything is fine. The user can, however do the following:

john@test.localhost=> UPDATE testrls.accounts SET username = 'john' WHERE
username = 'fred';
UPDATE 1
john@test.localhost=> SELECT * FROM testrls.accounts ;
 id | username |userinfo 
+--+-
  1 | john | Main accountant
  2 | john | Practitioner
(2 rows)

john@test.localhost=> DELETE FROM testrls.accounts WHERE id = 2;
DELETE 1
john@test.localhost=> SELECT * FROM testrls.accounts ;
 id | username |userinfo 
+--+-
  1 | john | Main accountant
(1 row)

The policy suggests that users can only modify rows where their name is in
the username field. In the UPDATE case the condition is tested against the
new values for the row, leading to a chance for any user to modify and
delete any row.

Obvioulsy there is a number of solutions to this issue. It would be enough
e.g. to modify the UPDATE grant on the table to avoid users in general to
modify this field as in the example in the documentation. In that case
everything would work correctly and the user would get a permission denied
message from the authorization system. I point this out to avoid answers to
my mail suggesting how to solve the problem. What I don't know or don't
understand is the following:

- Why is there not a consistent policy violation message when one would
apply as mentioned above?
- Why is the WITH CHECK condition only used on the values in the new record
in the case of an update?

Thank you.
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] Row level security - notes and questions

[Charles Clavadetscher]

Hi Stephen, hi Adrian

Thank you a lot! My huge mistake in understanding how policies work was to
assume that within a single policy FOR ALL the USING clause would be used
*only* for SELECT while WITH CHECK would be used by the modifying commands.
Now it is clear why it did not work as I wanted. I just checked it out on my
test environment and everything works as you said.

I thought I read the documentation carefully but I must have missed that
point. I will recheck to see if it really needs improvement, althought I
must admit that I am not an English native speaker.

And obviously I will continue with some experiments and get back with new
questions if any arise.

PostgreSQL has really a great community ;-)
Enjoy
Charles

> -Original Message-
> From: Stephen Frost [mailto:sfr...@snowman.net]
> Sent: Samstag, 11. Juli 2015 15:22
> To: Charles Clavadetscher
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Row level security - notes and questions
> 
> Charles,
> 
> * Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:
> > I have been testing the new row level security feature of 9.5 and I
> > have some notes and questions on it.
> 
> Great!  Glad to hear it.
> 
> > My first test is to enable row level security on the table without a
> > policy in place. According to the documentation this leads to a
> > general deny access.
> >
> > ALTER TABLE testrls.accounts ENABLE ROW LEVEL SECURITY;
> >
> > When user john tries to interact with the table he receives an empty
> > result set, instead of a policy violation error. There is no policy
> > yet, so this may be acceptable. I find it however confusing.
> 
> A permissions error would be thrown if the user didn't have access to the
> table through the GRANT system.  If no policy is found for a user (which
could
> happen multiple ways- no policies exist, policies exist but none apply to
this
> user, policies exist but none apply to this command,
> etc) then a default-deny policy is used which results in an empty set.
> 
> This is all documented, of course.  Specific suggestions for improving the
docs
> to help clarify this would certainly be appreciated.
> 
> > Since the result set is empty UPDATE and DELETE also do nothing.
> 
> Right, the default deny policy applies to all commands.
> 
> > In the case of an INSERT john receives a policy violation error. Still
> > there is no policy yet for the table. This seems not consistent with
> > the behaviour for the other commands.
> 
> INSERTs can fail where SELECTs, UPDATEs, and DELETEs do not- even when
> policies have been defined on the relation, and so this is consistent
within
> the overall policy system.  It would be inconsistent for SELECTs to fail
in all
> cases where INSERTs do.
> 
> The reason for this is that RLS is about filtering the rows returned, but
we
> suspend that for data which is being added to the system as we don't wish
to
> accept and then throw away data (which is what filtering on an INSERT, or
the
> result of an UPDATE, would do).
> 
> > For the next example I created a policy that allows users to read all
> > rows, but only change those "belonging" to them, identified by the
> > column username.
> 
> While I appreciate that your goal was to create such a policy, that's not
what
> this command does:
> 
> > CREATE POLICY accounts_policy ON testrls.accounts FOR ALL TO users
> > USING (true) WITH CHECK (username = SESSION_USER);
> 
> This command says "allow all commands to operate on all rows, but new
> rows being added to the system must have (username = SESSION_USER)".
> 
> A policy to allow users to read all rows would be:
> 
> CREATE POLICY accounts_policy ON testrls.accounts FOR SELECT TO users
> USING (true);
> 
> The following policy would then allow users to update rows which have
> (username = SESSION_USER):
> 
> CREATE POLICY accounts_policy_update ON testrls.accounts FOR UPDATE TO
> users USING (username = SESSION_USER);
> -- Note that with no WITH CHECK, the USING clause will be used
> 
> Further, the "passwd" example in the documentation covers exactly this
> policy of "read all, modify only same-user".
> 
> If you wanted to also allow INSERT and DELETE commands on rows which
> have (username = SESSION_USER), you could create policies for them, as
> so:
> 
> CREATE POLICY accounts_policy_insert ON testrls.accounts FOR INSERT TO
> users WITH CHECK (username = SESSION_USER);
> 
> CREATE POLICY accounts_policy_delete ON testrls.accounts FOR DELETE TO
> users USING (username = SESSION_USER);
> 
> > The policy suggests that users can only modify rows where their name
> >

Re: [GENERAL] A table of magic constants

[Charles Clavadetscher]

Hi Dane

 

There is a list of reserved keywords, including the ones that you mentioned.

 

  
http://www.postgresql.org/docs/9.5/static/sql-keywords-appendix.html

 

However you still need to search for their meaning.

 

Maybe it helps.

Bye

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dane Foster
Sent: Samstag, 11. Juli 2015 22:56
To: Adrian Klaver
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] A table of magic constants

 

Hello Adrian,

Thank you for the additional reference links but my concern was less about how 
to find out what a function (formerly magic constant) that I encountered in the 
wild did but more about having a list that would educate newcomers/me about 
what is automatically available for use. For example, in the RLS example from 
my original message, had I the same or similar need as the poster I would not 
have been able to formulate the policy that I quoted because I had no clue that 
SESSION_USER even existed. Specifically I would not have been able to formulate 
the following clause, "... WITH CHECK (username = SESSION_USER)", w/o first 
knowing that SESSION_USER was a thing.

 

​Regards,​




Dane

 

On Sat, Jul 11, 2015 at 3:54 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com> > wrote:

On 07/11/2015 12:21 PM, Raymond O'Donnell wrote:

On 11/07/2015 20:07, Dane Foster wrote:



As a recent convert to the Church of Postgres I've been consuming vast


Welcome to the One True Faith! :-)

amounts of information on PostgreSQL, and SESSION_USER is not the first
nor only, what I'm calling magic constant, that I've seen. Off the top
of my head, other examples that I've encountered are CURRENT_USER and
CURRENT_TIMESTAMP.

So my question is this, is there a reference table in the documentation
that I haven't found yet that lists all magic constants and their
meaning? And if not in the official documentation is it in the wiki?


session_user, current_timestamp and current_user are all functions, not
magic constants:

   http://www.postgresql.org/docs/9.4/static/functions-datetime.html
   http://www.postgresql.org/docs/9.4/static/functions-info.html

I hope this helps,

 

To add to this. In the documentation section:

http://www.postgresql.org/docs/9.4/interactive/index.html

there is a Search box where you can enter the word/phrase you are looking for.

If all else fails there is the Index:

http://www.postgresql.org/docs/9.4/interactive/index.html




Ray.



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

 

[GENERAL] Where to place suggestions for documentation improvements

[Charles Clavadetscher]

Hello

I have a generic question. Where should I/we place suggestions on possible
improvements of the documentation? Is it here or better on pgsql-docs?

Thanks
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] Where to place suggestions for documentation improvements

[Charles Clavadetscher]

Thank you Michael

I will post my suggestions on pgsql-docs to make sure ;-)

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Michael Paquier
> Sent: Dienstag, 14. Juli 2015 14:21
> To: Charles Clavadetscher
> Cc: PostgreSQL mailing lists
> Subject: Re: [GENERAL] Where to place suggestions for documentation
> improvements
> 
> On Tue, Jul 14, 2015 at 8:17 PM, Charles Clavadetscher
>  wrote:
> > I have a generic question. Where should I/we place suggestions on
> > possible improvements of the documentation? Is it here or better on
> pgsql-docs?
> 
> pgsql-docs may be a better place than this list dedicated to general
> questions, still most of the people picking up patches from -docs also usually
> have a look here.
> --
> 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] [9.5] question about row level security

[Charles Clavadetscher]

Hello

 

I am not so sure if this is feasible with policies only. Personally I would
separate private data from the rest assuming that that part is anyway
information that also the boss is not supposed to look at. A separation
would make the setup of policies easy.

 

Using the structure that you described I could implement the use case, but
it is neither elegant nor particularly nice and I am not sure If there are
not some flaws somewhere. What I did is use a function that returns for a
name the content of col_privat and compare it with the current value in the
with check clause.

 

Well here it goes, but again. The idea to separate private and business is
much better... ;-)

 

create table public.staff (

id integer,

name text,

boss text,

col1 integer,

col_privat text

);

 

create role boss login password 'xxx';

create role joe login password 'xxx';

create role sue login password 'xxx';

 

insert into staff values (1,'boss','boss',10,'boss privat'),

(2,'joe','boss',20,'joe privat'),

(3,'sue','boss',30,'sue privat');

 

grant select, update on staff to boss;  -- Assuming that boss can change
basicall everything.

grant select, update (col_privat) on staff to sue, joe;  -- assuming that
normal user should not change other fields.

 

alter table staff enable row level security;

 

 

-- Let people see their own entries

CREATE POLICY all_users ON staff

FOR SELECT

TO sue, joe

USING (name = SESSION_USER);

 

-- Let boss see all entries

CREATE POLICY for_boss ON staff

FOR SELECT

TO boss

USING (true);

 

-- Let people and boss only change their own record

CREATE POLICY change_own_private ON staff

FOR UPDATE

TO boss, sue, joe

USING (name = SESSION_USER);

 

-- Create a function to check the content of col_privat

CREATE OR REPLACE FUNCTION public.get_col_privat(p_name text)

RETURNS text

AS $$

BEGIN

  RETURN (SELECT col_privat FROM public.staff

  WHERE name = $1);

END;

$$ LANGUAGE plpgsql;

 

GRANT EXECUTE ON FUNCTION get_col_privat(text) TO boss;

 

-- Allow boss to see modify all entries where he is the boss

-- but if the record does not belong to him and the content of col_privat is
different from the original then do not allow to create the new row.

CREATE POLICY change_all_but_privat ON staff

FOR UPDATE

TO boss

USING (boss = SESSION_USER)

WITH CHECK (name <> SESSION_USER AND col_privat = (SELECT
get_col_privat(name)));

 

Testing (notice which user is doing what on the left):

 

sue=> SELECT * FROM staff;

id | name | boss | col1 | col_privat

+--+--+--+

  3 | sue  | boss |   30 | sue privat

(1 row)

 

sue=> UPDATE staff SET col_privat = 'sue changed' WHERE name = 'sue';

UPDATE 1

sue=> UPDATE staff SET col_privat = 'sue changed' WHERE name = 'joe';

UPDATE 0

 

boss=> SELECT * FROM staff;

id | name | boss | col1 | col_privat

+--+--+--+-

  1 | boss | boss |   10 | boss privat

  2 | joe  | boss |   20 | joe privat

  3 | sue  | boss |   30 | sue changed

(3 rows)

 

Boss sees everything.

 

boss=> UPDATE staff SET col1 = 250 WHERE name = 'sue';

UPDATE 1

boss=> UPDATE staff SET col_privat = 'boss changed' WHERE name = 'sue';

ERROR:  new row violates row level security policy for "staff"

 

Boss can change other fields but not col_privat.

 

boss=> UPDATE staff SET col_privat = 'boss changed' WHERE name = 'boss';

UPDATE 1

 

But he can change his own col_privat.

 

boss=> SELECT * FROM staff;

id | name | boss | col1 |  col_privat

+--+--+--+--

  2 | joe  | boss |   20 | joe privat

  3 | sue  | boss |  250 | sue changed

  1 | boss | boss |   10 | boss changed

 

Hope this helps.

Bye

Charles

 

> -Original Message-

> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-

> ow...@postgresql.org] On Behalf Of Andreas Kretschmer

> Sent: Mittwoch, 15. Juli 2015 09:55

> To: pgsql-general@postgresql.org

> Subject: [GENERAL] [9.5] question about row level security

> 

> Hello @ll,

> 

> how can i define a policy to prevent update a single field but enable
update

> other fields in a row?

> 

> For instance, a staff member table. the boss should be able to change all

> fields but not a specific field in all rows, but should be able to update
this field

> for his own row.

> 

> id | name | boss |col1 | col_privat

> 

>  1 | boss | boss |  10 | boss privat

>  2 | joe  | boss |  20 | joe privat

>  3 | sue  | boss |  30 | sue privat

> 

> boss should be able to edit all except col_privat for id=2 and 3, but he
should

> be able update this column for id=1.

> Therefore i can't revoce the update-priv for this column.

> 

> create policy enable_boss on  for update using

> (boss=current_user) with check (???)

> 

> Is there a way to access old.* and new.* like within a trigger?

> (something like with check(old.col_privat=new.col_privat))

> 

> 

> 

> Andreas

> --

> Really, I'm not out to dest

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

[Charles Clavadetscher]

Hello

> I've just started to read through postgres-9.5 "what's new" ... before giving 
> it
> a try. The "insert ... on conflict do update" is particularly atractive to 
> me; but I
> was wondering why it does not cover the third usage scenario of action that a
> programmer may need for a PK conflict during insert.
> 
> In my experience, most often I generate a random value for PK, with that
> random value becoming a unique ticket like a voucher (related to monetary
> value). for that I:
> 
> CREATE TABLE vouchers (a_voucher bigint PRIMARY KEY default
> (random()*10)::bigint, issued date default now(), .);
> 
> Naturally:
> 1. A_VOUCHER range space is always significantly larger then currently issued
> voucher count - so conflicts are rare.
> 2. with current (as of 9.5) implementation I think I can always "ON CONFLICT
> DO NOTHING", and retry the INSERT from application level.

An UPSERT is "try an INSERT and if there is a conflict, do nothing or UPDATE 
some values of the existing record". The scenario that you suggest is not an 
UPSERT, because what you want to reach is to try a  new INSERT, hoping that 
this works.
What speak against using a sequence for the primary key column a_voucher? This 
would guarantee that you don't have a conflict.

> But it would be immenensly more comfortable if one could: "INSERT ... ON
> CONFLICT (a_voucher) DO RETRY"; with semantics of that statement being:
> 1. prepare should check if there is a DFAULT for specified "conflict column"
> (here: "a_voucher"), and fail if there isn't one.
> 2. prepare shoud check if the default is a VOLATILE function... or fail.
> 3. when all that pass, the prepared insert, when executed and with a conflict,
> should be re-attempt with NEW call to that DEFAULT function of the
> indicated CONFLICT column(s).
> 3. and there should be a /ETC/POSTGRES.CONF parameter limiting the
> number of retries for a single conflict - as a programmer I know, that if I 
> need
> to retry more then twice, the space is too dense, always. So I need to change
> the DFAULT function, not increase the retry_count ...
> thus haveing DDS allowing the change to the DFAULT FUNCTION means it's
> not necesary to allow for change of the RETRY_CONT (during database
> life) - and when the later is in the CONFIG, the less it's prone to typo 
> errors of
> application authors.
> 
> Was the above considered for "ON CONFLICT" implementation before?
> 
> If so, can someone pls point me to critics it received.
> 
> If not: is it unreasonable? why?

IMHO, as I mentioned, this is not an UPSERT use case, but maybe the 
implementors of the feature may have different arguments. You could implement 
that in a function instead of the application, if you prefer.

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] Problem with pl/python procedure connecting to the internet

[Charles Clavadetscher]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Igor Sosa Mayor
> Sent: Sonntag, 23. August 2015 07:22
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Problem with pl/python procedure connecting to the
> internet
> 
> Adrian Klaver  writes:
> 
> > Are you taking lack of coordinates in the table as evidence of no
> > connection or are you determining that some other way?
> 
> thanks again for your answer. The evidence is very easy. I get this[1].
> BUT: if I try exactly the same python code in a console there is no error
and
> the coordinates are there.
> 
> > Have you tried using plpy.notice in plpythonu and RAISE NOTICE in
> > plpgsql to track what is happening?
> 
> Yes, if the connection works, I get the correct answer. As I say: for some
> reason postges can not make the connection OR get the answer (but not
> always, it has worked only 3-4 times). All other things are working:
> internet connection, python code...
> 
> 
> [1]
> ERROR:  geopy.exc.GeocoderServiceError:  o servicio desconocido>
> CONTEXTO:  Traceback (most recent call last):
>   PL/Python function "geocodificar", line 4, in 
> location = geoloc.geocode(direccion)
>   PL/Python function "geocodificar", line 190, in geocode
>   PL/Python function "geocodificar", line 159, in _call_geocoder función
> PL/Python «geocodificar»
> sentencia SQL: «SELECT * from geocodificar(NEW.ciudad || ' '
||
> NEW.pais)»
> función PL/pgSQL anadirgeocoord() en la línea 5 en sentencia SQL

Are you sure that you really use exactly the same code? The indication
"survice unknown" seems to point to an error in the URL and not to an
authorization problem. Maybe some encoding problem?
The irritating thing is that you mention that it works sometimes and
sometimes not. Getting back to the encoding problem idea, is there a
difference if you call the function with a name containing special
characters (e.g. "é", "à", "ñ") or not?

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] Problem with pl/python procedure connecting to the internet

[Charles Clavadetscher]

Hi

Weird. According to
http://geopy.readthedocs.org/en/latest/#geopy.exc.GeocoderServiceError this
is the most generic exception, only used when there is not a more specific
one (which include by the way problems caused by connectivity and
authorization). The message of the exception is supposed to be the one
delivered by the original application. In this case: "Nombre o servicio
desconocido":

I am wondering if there is an error message matching the one you receive in
PostgreSQL.

> > Are you sure that you really use exactly the same code? The indication
> > "survice unknown" seems to point to an error in the URL and not to an
> > authorization problem. Maybe some encoding problem?
> 
> THnaks for your hints.
> 
> Yes, exactly the same code. Exactly the same query: two consoles, one with
> postgres, other with ipython. Postgres gives the error; ipython works.

Somehow I expected that answer. I am not a python expert, but it happens
that different pieces of code at the end do not use the same libraries or
framework code by configuration. But if you are positive on that, no more
discussion.

> > The irritating thing is that you mention that it works sometimes and
> > sometimes not. Getting back to the encoding problem idea, is there a
> > difference if you call the function with a name containing special
> > characters (e.g. "é", "à", "ñ") or not?
> 
> No special characters in the query. To be honest: it worked only 2-3 times
at
> work and I thought 'finally I managed to do it work'. But since then, it
is not
> working anymore...

Well, if it worked a few times and then never again, then something must
have changed in your system. This could also be a possible reason for the
error.
Sorry for asking obvious things, but it happened to me to oversee some of
them in the past: Are you making the calls from the same computer?

> In any case: I'm pretty sure it is a problem of postgres which is not able
to
> make the connection. If I put a parameter timeout=15, postgres gives
> immediately the same error.

So it looks like a timeout problem with a confusing error message.
A possible way to follow this up would be to call another webservice from a
python function in PG and see if it behaves the same.
Another way to analyze the problem could be to sniff the network traffic and
see what really happens when you make the function call. This could help at
least to discard network problems and may lead to some useful information.

I hope that you will find a 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] Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT

[Charles Clavadetscher]

Hello

 

You declare your variable r as of type 
application.store_ldap_profile_defaults%rowtype, but then select only 4 of the 
5 fields of the table to put in there. The last one (happens to be access_mode 
is then null).

The structures don’t match. That may explain this behaviour.

 

This works:

 

CREATE OR REPLACE FUNCTION init_store_ldap_profiles() RETURNS TRIGGER AS $$

DECLARE

   r application.store_ldap_profile_defaults%rowtype;

BEGIN

   FOR r IN

 SELECT id, ref_ldap_department, ref_ldap_title, format, 
access_mode FROM application.store_ldap_profile_defaults WHERE format = 
NEW.format

   LOOP

 INSERT INTO application.store_ldap_profile (ref_store, 
ref_ldap_department, ref_ldap_title, access_mode) VALUES (NEW.id, 
r.ref_ldap_department, r.ref_ldap_title, r.access_mode);

   END LOOP;

   RETURN NEW;

END; $$

LANGUAGE plpgsql VOLATILE;

 

Bye

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Christopher BROWN
Sent: Donnerstag, 27. August 2015 13:50
To: pgsql-general@postgresql.org
Subject: [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT

 

Hello,

 

I'm new to this list but have been using PostgreSQL for a moment.  I've 
encountered an error using PostgreSQL 9.4.4 which can be reproduced using the 
SQL below.

 

The trigger "init_store_ldap_profiles_trigger" fails if the function 
"init_store_ldap_profiles()" is written as below.  If I rewrite it to use 
"SELECT * FROM ...", instead of "SELECT id, ref_ldap_department, 
ref_ldap_title, access_mode FROM ...", it works.

 

This is the error I get:

ERROR: null value in column "access_mode" violates not-null constraint

  Detail: Failing row contains (1, 2015-08-27 13:37:24.306883, 2015-08-27 
13:37:24.306883, 1, 1, 1, null).

  Where: SQL statement "INSERT INTO application.store_ldap_profile (ref_store, 
ref_ldap_department, ref_ldap_title, access_mode) VALUES (NEW.id, 
r.ref_ldap_department, r.ref_ldap_title, r.access_mode)"

PL/pgSQL function init_store_ldap_profiles() line 8 at SQL statement

 

It seems that for some reason, the column 
"store_ldap_profile_defaults.access_mode" appears to be NULL when referred to 
using r.access_mode (r being the declared %ROWTYPE).  I can modify the WHERE 
clause to add a dummy condition on "access_mode", and that works (as in, it 
doesn't solve my problem but the column value is visible to the WHERE clause).

 

Is this a bug or can I fix this in my SQL ?

 

Thanks,

Christopher

 

Here's the SQL :

 

 

CREATE SCHEMA application;

SET search_path TO application;

 

CREATE TABLE IF NOT EXISTS store (

  id SERIAL PRIMARY KEY,

  ctime TIMESTAMP NOT NULL DEFAULT now(),

  mtime TIMESTAMP NOT NULL DEFAULT now(),

  is_archived NUMERIC(1) CHECK (is_archived IN (1,0)) 
DEFAULT 0,

  name VARCHAR(200) NOT NULL CHECK (length(name) > 0),

  hrcompany VARCHAR(200) NOT NULL CHECK (length(hrcompany) 
> 0),

  hrsite VARCHAR(200) NOT NULL CHECK (length(hrsite) > 0),

  format VARCHAR(1) NOT NULL CHECK (format IN ('H', 'S')),

  UNIQUE (hrcompany, hrsite)

);

 

CREATE INDEX ON store (mtime);

CREATE INDEX ON store (is_archived);

CREATE INDEX ON store (format);

 

 

CREATE TABLE IF NOT EXISTS ldap_department (

  id SERIAL PRIMARY KEY,

  ctime TIMESTAMP NOT NULL DEFAULT now(),

  mtime TIMESTAMP NOT NULL DEFAULT now(),

  code VARCHAR(20) NOT NULL CHECK (code ~ '[0-9]+'),

  label VARCHAR(200) NOT NULL CHECK (length(label) > 0),

  UNIQUE(code)

);

 

CREATE INDEX ON ldap_department (mtime);

 

 

CREATE TABLE IF NOT EXISTS ldap_title (

  id SERIAL PRIMARY KEY,

  ctime TIMESTAMP NOT NULL DEFAULT now(),

  mtime TIMESTAMP NOT NULL DEFAULT now(),

  code VARCHAR(20) NOT NULL CHECK (code ~ '[0-9]+'),

  label VARCHAR(200) NOT NULL CHECK (length(label) > 0),

  UNIQUE(code)

);

 

CREATE INDEX ON ldap_title (mtime);

 

 

CREATE TABLE IF NOT EXISTS store_ldap_profile_defaults (

  id SERIAL PRIMARY KEY,

  ref_ldap_department INTEGER NOT NULL,

  ref_ldap_title INTEGER NOT NULL,

  format VARCHAR(1) NOT NULL CHECK (format IN ('H', 'S')),

  access_mode VARCHAR(1) NOT NULL CHECK (access_mode IN 
('R', 'W')),

  FOREIGN KEY (ref_ldap_department) REFERENCES 
ldap_department (id) ON DELETE CASCADE,

  FOREIGN KEY (ref_ldap_title) REFERENCES ldap_title (id) 
ON DELETE CASCADE,

  UNIQUE (ref_ldap_department

Re: [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT

[Charles Clavadetscher]

Another possibility is

 

CREATE OR REPLACE FUNCTION init_store_ldap_profiles() RETURNS TRIGGER AS $$

DECLARE

   r RECORD;

BEGIN

   FOR r IN

 SELECT id, ref_ldap_department, ref_ldap_title, access_mode FROM 
application.store_ldap_profile_defaults WHERE format = NEW.format

   LOOP

 INSERT INTO application.store_ldap_profile (ref_store, 
ref_ldap_department, ref_ldap_title, access_mode) VALUES (NEW.id, 
r.ref_ldap_department, r.ref_ldap_title, r.access_mode);

   END LOOP;

   RETURN NEW;

END; $$

LANGUAGE plpgsql VOLATILE;

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles Clavadetscher
Sent: Donnerstag, 27. August 2015 14:57
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP ... 
INSERT

 

Hello

 

You declare your variable r as of type 
application.store_ldap_profile_defaults%rowtype, but then select only 4 of the 
5 fields of the table to put in there. The last one (happens to be access_mode 
is then null).

The structures don’t match. That may explain this behaviour.

 

This works:

 

CREATE OR REPLACE FUNCTION init_store_ldap_profiles() RETURNS TRIGGER AS $$

DECLARE

   r application.store_ldap_profile_defaults%rowtype;

BEGIN

   FOR r IN

 SELECT id, ref_ldap_department, ref_ldap_title, format, 
access_mode FROM application.store_ldap_profile_defaults WHERE format = 
NEW.format

   LOOP

 INSERT INTO application.store_ldap_profile (ref_store, 
ref_ldap_department, ref_ldap_title, access_mode) VALUES (NEW.id, 
r.ref_ldap_department, r.ref_ldap_title, r.access_mode);

   END LOOP;

   RETURN NEW;

END; $$

LANGUAGE plpgsql VOLATILE;

 

Bye

Charles

 

From: pgsql-general-ow...@postgresql.org 
<mailto:pgsql-general-ow...@postgresql.org>  
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Christopher BROWN
Sent: Donnerstag, 27. August 2015 13:50
To: pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org> 
Subject: [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT

 

Hello,

 

I'm new to this list but have been using PostgreSQL for a moment.  I've 
encountered an error using PostgreSQL 9.4.4 which can be reproduced using the 
SQL below.

 

The trigger "init_store_ldap_profiles_trigger" fails if the function 
"init_store_ldap_profiles()" is written as below.  If I rewrite it to use 
"SELECT * FROM ...", instead of "SELECT id, ref_ldap_department, 
ref_ldap_title, access_mode FROM ...", it works.

 

This is the error I get:

ERROR: null value in column "access_mode" violates not-null constraint

  Detail: Failing row contains (1, 2015-08-27 13:37:24.306883, 2015-08-27 
13:37:24.306883, 1, 1, 1, null).

  Where: SQL statement "INSERT INTO application.store_ldap_profile (ref_store, 
ref_ldap_department, ref_ldap_title, access_mode) VALUES (NEW.id, 
r.ref_ldap_department, r.ref_ldap_title, r.access_mode)"

PL/pgSQL function init_store_ldap_profiles() line 8 at SQL statement

 

It seems that for some reason, the column 
"store_ldap_profile_defaults.access_mode" appears to be NULL when referred to 
using r.access_mode (r being the declared %ROWTYPE).  I can modify the WHERE 
clause to add a dummy condition on "access_mode", and that works (as in, it 
doesn't solve my problem but the column value is visible to the WHERE clause).

 

Is this a bug or can I fix this in my SQL ?

 

Thanks,

Christopher

 

Here's the SQL :

 

 

CREATE SCHEMA application;

SET search_path TO application;

 

CREATE TABLE IF NOT EXISTS store (

  id SERIAL PRIMARY KEY,

  ctime TIMESTAMP NOT NULL DEFAULT now(),

  mtime TIMESTAMP NOT NULL DEFAULT now(),

  is_archived NUMERIC(1) CHECK (is_archived IN (1,0)) 
DEFAULT 0,

  name VARCHAR(200) NOT NULL CHECK (length(name) > 0),

  hrcompany VARCHAR(200) NOT NULL CHECK (length(hrcompany) 
> 0),

  hrsite VARCHAR(200) NOT NULL CHECK (length(hrsite) > 0),

  format VARCHAR(1) NOT NULL CHECK (format IN ('H', 'S')),

  UNIQUE (hrcompany, hrsite)

);

 

CREATE INDEX ON store (mtime);

CREATE INDEX ON store (is_archived);

CREATE INDEX ON store (format);

 

 

CREATE TABLE IF NOT EXISTS ldap_department (

  id SERIAL PRIMARY KEY,

  ctime TIMESTAMP NOT NULL DEFAULT now(),

  mtime TIMESTAMP NOT NULL DEFAULT now(),

  code VARCHAR(20) NOT NULL CHECK (code ~ '[0-9]+'),

  label VARCHAR(200) NOT NULL CHECK (length(label) > 0),

  UNIQUE(code)

);

 

CREATE INDEX ON ldap_department (mt

Re: [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT

[Charles Clavadetscher]

Hi

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Adrian Klaver
> Sent: Donnerstag, 27. August 2015 15:41
> To: Christopher BROWN 
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP ...
> INSERT
> 
> On 08/27/2015 06:33 AM, Christopher BROWN wrote:
> > Hello Adrian,
> >
> > Yep, Charles' explanation helped me understand what was going on.
> > Before that, I was as confused as you were (in your first reply) about
> > how access_mode could be NULL (with the same reasoning).  In any case,
> > thanks for your links ; I did try searching the web for the answer
> > before posting, but got too many irrelevant results given that I had to
> > search using very common terms.
> 
> Yeah, I did not get Charles's second post until I sent my second, so it
> was redundant.
> 
> >
> > I've concluded the the RECORD type is the best-fit for my approach.  I
> > don't know if it's any faster that using SELECT * with a specific
> > %ROWTYPE given that the data doesn't go anywhere outside the function
> > body.  I don't know if the order in which columns are returned (by
> > either SELECT * or using explicit column names matters when using
> > %ROWTYPE), although I'll assume that PostgreSQL is smart enough to match
> > things up correctly, if I need to write a function that returns
> > instances of any given %ROWTYPE in the future.
> 
> I don't know, I have always just used SELECT * as I needed all the
> columns anyway.

It is probably a matter of taste, more than best practices. I find the variant 
with RECORD better, because you only search for the fields that you need. In 
this case it may make a little difference, but if you happen to have a table 
with many more columns, it would be a waste of resources, unless, as in 
Adrian's case, you do need all the fields. While using a rowtype then SELECT * 
is guaranteed, IMHO, to return that record's fields in the correct order. 
Listing the fields explicitly, as you already noticed, could lead to a 
maintenance nightmare if anything changes in the table structure. In both cases 
you are still not safe against, e.g. changes of column names. But I guess that 
this is not an issue so far.

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] Need Database Backup

[Charles Clavadetscher]

Hi

I agree with all upthreads mentioning that the amount of information you 
(Murali) deliver is much too little to help. Nevertheless my thoughts.

- NTLDR Missing is obviously a Windows problem and I don't think that it has to 
do with PostgreSQL. You may find better help on a Windows forum or mailing list.
- If you have produced a SQL file dumping your whole clusterv (pg_dumpall), 
then you should only need to reinstall PostgreSQL and run the file as a script 
(from command line or psql). There is no upload of sql file, unless I don't 
understand what you mean.

Bye
Charles

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Marlowe
Sent: Sonntag, 30. August 2015 15:36
To: muralih...@rediffmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need Database Backup

OK we're gonna need to keep this on the list, as other folks may be a much 
better choice to help you than me (I'm not a Windows guy.)
OK so we need more info to help. I don't know how to fix the NTLDR problem, 
etc, because it's windows. However, the folks who might be able to help need 
more info.
Specifically, what are you doing to try and load the .sql extension? 
Are you using a second machine for this? 
Have you made a backup of the files in d:\Program Files\Postgresql and put it 
in a safe place? It's important to NOT mess around with the only copy in case 
things go wrong with it. 
What exactly HAVE you done to try and move the PostgreSQL directory elsewhere? 
What EXACT errors are you seeing. 
The more information you provide the more we can help. Right now you're asking 
for help, but you're not helping yourself, because you're not showing us what's 
going wrong. More details gets you more answers.

On Sun, Aug 30, 2015 at 3:39 AM, Murali N Rao  wrote:
Sir,

Suddenly Our system got restarted and we got a msg NTLDR Missing. I tried to 
repair but not able.

OS: windows 2003 Server

We installed the PGSQL in D:\program files\Postgresql

I have whole folder having capacity of 1.53 GB

and i have recently taken backup on 01st of August with Extension (.sql) even i 
cant upload that.

Please i request u to do the need ful

Regards

Murali N
System Admin,
CIT, Gubbi


From: Scott Marlowe 
Sent: Sun, 30 Aug 2015 09:34:42 
To: muralih...@rediffmail.com
Cc: "pgsql-general@postgresql.org" 
Subject: Re: [GENERAL] Need Database Backup
On Fri, Aug 28, 2015 at 11:27 PM, Murali N Rao
 wrote:

> Sir,
>
> We were using newgenlib software which is having postgresql as backend.
>
> Suddenly Our Systems OS got corrupted and unable to start and we have taken 
> installed folder as backup from the drive but we are unable to restore and 
> kindly help in this.
>
> and also we have also taken backup 30 days in .sql format but even i am 
> unable to upload.
>
> So, i kindly request u to help in this as the database contains more than 
> 2000 Students data
>

OK let's see what we can do to help you. What OS are you running? How
idd you move the database directory to the new machine? What errors
are you getting when you to start the db? What errors are you getting
when trying to restore from your old .sql backup? What version of
pgsql are you running?

Just kinda imagine one of us is looking over your shoulder and tell us
everything. Details that may seem small and unimportant can be very
important in helping to troubleshoot this kind of thing.

Get your own FREE website, FREE domain & FREE mobile app with Company email.  
Know More >



-- 
To understand recursion, one must first understand recursion.



-- 
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] Need Database Backup

[Charles Clavadetscher]

Well the rest should be easy.

psql -U ... -h ... -p ... -d ... -f sql_file.sql

http://www.postgresql.org/docs/9.4/static/app-psql.html option "f"

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Melvin Davidson
Sent: Sonntag, 30. August 2015 16:27
To: Adrian Klaver 
Cc: Scott Marlowe ; muralih...@rediffmail.com; 
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need Database Backup

Adrian
From Murali N Rao 5:43 AM (4 hours ago)

>now i have installed new os and installed PGSQL again it working now but no 
>data

On Sun, Aug 30, 2015 at 10:21 AM, Adrian Klaver  
wrote:
On 08/30/2015 07:13 AM, Melvin Davidson wrote:
You need to recreate the database to reload the data into so

from the windows command line:
C:\PostgresDB\bin\createdb.exe -U postgres ;

That assumes Postgres is running, which at this point seems not to be the case 
or at least is unproven.

Then to restore the data from the sql backup
C:\PostgresDB\bin\pg_restore.exe -U postgres 

This will not work if the *.sql is a plain text file.




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



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



-- 
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] Public facing PostgreSQL hosting ?

[Charles Clavadetscher]

Hello

You can use pgAdmin (or psql for that matter) to connect to a database over the 
network, provided PostgreSQL is configured to accept this kind of connections 
and all the firewalls to it allow the traffic.

http://www.postgresql.org/docs/9.4/interactive/runtime-config-connection.html
http://www.postgresql.org/docs/9.4/interactive/client-authentication.html

A tool I heard about, but never used is phpPgAdmin. You may want to take a 
look: http://phppgadmin.sourceforge.net/doku.php

Bye
Charles

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of essam Ganadily
Sent: Montag, 31. August 2015 09:04
To: pgsql-general@postgresql.org
Subject: [GENERAL] Public facing PostgreSQL hosting ?

hi
i do develop on PostgreSQL from home and from work. i need public facing 
PostgreSQL , something i can use Pgadmin from anywhere.
performance and scalability is not important because i will be running like few 
operations per day.

any idea where can i find that ?
thankx



-- 
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] Delete trigger

[Charles Clavadetscher]

Hello

Not sure I get it right, but all three fields are not nullable. So they will 
always have a value, which is what I understand of "are specified".
What do you need the trigger for in that case?

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Leif Jensen
> Sent: Freitag, 18. September 2015 10:23
> To: pgsql-general 
> Subject: [GENERAL] Delete trigger
> 
>Hi,
> 
>I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:
> 
> CREATE TABLE devicegroup (
> groupid integer NOT NULL,
> ctrlid integer NOT NULL,
> userid integer NOT NULL
> );
> ALTER TABLE ONLY devicegroup
> ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);
> 
>I want to make sure that records are only deleted when all 3 fields are 
> specified, so I tried make a trigger:
> 
> CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH STATEMENT
> EXECUTE PROCEDURE deleteUserDev();
> 
> which could check for NOT NULL on the 3 fields before actual doing the 
> delete. Unfortunately this is not possible to
> do FOR EACH STATEMENT, and FOR EACH ROW would not give me the chance to check 
> for NOT NULL.
> 
>Any ideas ?
> 
>  Leif
> 
> 
> --
> 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 show time zone with numerical offset in CSV log?

[Charles Clavadetscher]

Hi

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Michael Zoet
> Sent: Dienstag, 22. September 2015 12:07
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] how to show time zone with numerical offset in CSV log?
> 
> Hello everybody,
> 
> I am saving PostgreSQL log file data (CVS logs) with Logstash and
> Elasticsearch. My problem with this is that the time zone value is
> with the name of the time zone like
> 
>   2015-09-22 12:02:59.836 CEST
> 
> which Logstash can not process.
> 
> What Logstash needs are date/time stamps like
> 
> 2015-09-22 12:02:59.836 +0200.
> 
> How can I setup Postgres to log with a numerical offset in the CSV
> logs and not with the name of the time zone?

Not exactly the same format, but this may help:

kofadmin@kofdb.localhost=> SET datestyle TO 'ISO';
SET
kofadmin@kofdb.localhost=> select now();
now

 2015-09-22 12:53:38.123+02
(1 row)

If you want the change to be persistent you can use:

ALTER DATABASE database_name SET datestyle TO 'ISO';

And then reconnect to see the change.

Bye
Charles

> 
> Any hints and links to the corresponding documentation would be appreciated.
> 
> Regards,
> 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] how to show time zone with numerical offset in CSV log?

[Charles Clavadetscher]

Hi

> thanks for the quick response and it looked promising but did not work
> as expected.
> 
> I can set the datestyle to ISO on database level but this does not
> seem to effect the way the CSV logs are written. I still get
> 2015-09-22 13:06:01.658 UTC (or CEST and so on) in the log files. And
> as I see it is not only in the CSV logs, also in the none CSV logs I
> have.

I guess this means that the datestyle affects the way how the client wants the 
information displayed.

> Is there a way to convince Postgres to write the date/time with
> numerical time zone values to the log files?

Unfortunately I am a bit short of time right at the moment. I would suggest 
that you look into how the tools you are using generate the csv.

Here what I could see using psql:

db=> create table testdate (timedate timestamptz);
CREATE TABLE
db=> \d testdate
 Table "public.testdate"
  Column  |   Type   | Modifiers
--+--+---
 timedate | timestamp with time zone |

db=> insert into testdate values(clock_timestamp());
INSERT 0 1
db=> insert into testdate values(clock_timestamp());
INSERT 0 1
db=> insert into testdate values(clock_timestamp());
INSERT 0 1

kofadmin@kofdb.localhost=> SET datestyle TO "GERMAN";
SET
db=> \copy testdate to testdate.csv csv header ;
COPY 3

Content of file:

timedate
22.09.2015 15:53:48.268 CEST
22.09.2015 15:53:49.612 CEST
22.09.2015 15:53:50.44 CEST

db=> SET datestyle TO "ISO";
SET
db=> \copy testdate to testdate.csv csv header ;
COPY 3

Content of file:

timedate
2015-09-22 15:53:48.268+02
2015-09-22 15:53:49.612+02
2015-09-22 15:53:50.44+02

So it is actually a matter of instructing the client to print the date in the 
format that you wish.

Hope this helps.
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] Selecting pairs of numbers

[Charles Clavadetscher]

> aklaver@test=> create table pr_test(x int, y int);
> 
> aklaver@test=> select * from pr_test  where (x, y) between (1, 3) and
> (3,2) order by x,y;
>   x | y
> ---+---
>   1 | 3
>   1 | 4
>   2 | 1
>   2 | 2
>   2 | 3
>   2 | 4
>   3 | 1
>   3 | 2

+1, nice.



-- 
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] Unable to select a table as postgres user

[Charles Clavadetscher]

Hi

What error do you get?

- Permission denied or table does not exist? Latter would indicate a 
problem with the search_path and you should fully qualify the table name 
in the function body.


- Did you create the function as postgres user?

Bye
Charles

On 30/10/2015 07:56, rajan wrote:

Yes. I agree that the superuser cannot be restricted with any access.

But my scenarios is, I am executing a function(VOLATILE SECURITY DEFINER)
using the superuser and it function fails with unable to select a particular
table.

At the same time, I am able to select the table as a superuser, by executing
select * from table_name;



--
View this message in context: 
http://postgresql.nabble.com/Unable-to-select-a-table-as-postgres-user-tp5872036p5872041.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





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

Re: [GENERAL] Selectively Importing Data

[Charles Clavadetscher]

Hello

This should work:

\copy taxon (descr) from  ;

This is true if your sheet has only one column, so you should delete that 
column. However if you happen to have another table with a foreign key on 
taxonid you will probably screw up the references. But from your description I 
take it, that this is not the case.

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Raymond O'Donnell
> Sent: Samstag, 31. Oktober 2015 12:42
> To: David Blomstrom ; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Selectively Importing Data
> 
> On 31/10/2015 00:24, David Blomstrom wrote:
> > First consider the following table:
> >
> > create table taxon (
> >   taxonid serial,
> >   descr text
> > );
> >
> > As I understand it, "serial" means that column will automatically
> > populate with a numerical key.
> >
> > If I want to fill the field 'descr' with a list of scientific names
> > stored in a spreadsheet, then how would I proceed?
> >
> > I have a spreadsheet that has about a dozen columns. I copied the field
> > with scientific names into a new spreadsheet. Then I created an empty
> > field on the left. So I have a spreadsheet with two columns. I saved it
> > as a CSV file.
> >
> > When I try to import it with pgAdmin III, I get the error message
> >
> > WARNING: null value in column "taxonid" violates non-null constraint
> >
> > How can I import that single column into the second column in this
> > table? Thanks.
> 
> Serial columns will only get populated if there's no value (or no NULL
> either) inserted into that column. "Serial" [1] is not a real data type;
> it's just a handy shorthand for creating a sequence, setting a DEFAULT
> of nextval([sequence name]) on the column, and making that column NOT NULL.
> 
> At a guess, I think that - by creating the blank column in the
> spreadsheet and then importing from it - you were actually inserting
> NULL into the taxonid column, hence violating the NOT NULL constraint.
> 
> You need to find some way of excluding the taxonid column from the
> import, so that the DEFAULT mechanism will populate it for you. I don't
> know how you do that with pgAdmin; I know it can be done with COPY from
> the command-line. You could try posting to the pgAdmin mailing list [2]
> about it.
> 
> I hope that helps,
> 
> Ray.
> 
> 
> [1]
> http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL
> 
> [2] http://www.pgadmin.org/support/list.php
> 
> 
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
> 
> 
> --
> 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] 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] 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] 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] Proposal "stack trace" like debugging option in PostgreSQL

[Charles Clavadetscher]

Hello

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

Edson Richter  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] 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] 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] 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] 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' ; 'pgsql-general' 
> 
> 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 
> > 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 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 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] 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;
> _value2  := _values[_co

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

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]

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

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

 

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 mailto:adrian.kla...@aklaver.com> >:

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  

 

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

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 ; Charles Clavadetscher 
> 
> 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  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] 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] pg_sample

[Charles Clavadetscher]

Hello

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


-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Patrick B  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] 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] 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 ; 
> 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 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 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' ; 'John R Pierce' 
> ; 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 ; 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
> > in postgresql-9.3.6
> >
> > On 11/16/2016 6:

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

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 
> mailto:lee.hachadooria...@gmail.com>
> > 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] 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] 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 mailto:durumd...@gmail.com> >:

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] 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 
> 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 
>  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 assumed that this kind of measurements
&g

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
> >>
> >
> > The file also contains line "BEGIN TRA

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] 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 
> 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 :
> > 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 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' 
> 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 
> > 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 
> > :
> > > 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
>

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

 &utm_medium=referral&utm_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] 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 = off
> log_timezone = 'UTC'
>

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

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

Re: [GENERAL]

[Charles Clavadetscher]

Hello

 

I never used pg_restore, but I assume that it works similarly to other PG 
tools, i.e. if you don’t specify a user to connect to the database it will take 
your unix user name. I assume that your step number 6 is the closing of the 
shell where you were as user postgres because after 5 you already are out of 
psql.

 

So the questions is, if you have a DB user alex with the Create privilege on 
the database icare.

 

If not you may try the following:

 

-Open a psql terminal (steps 1-3)

-CREATE ROLE alex LOGIN SUPERUSER PASSWORD '...';

 

And try the pg_restore again (steps 5-7).

 

You may also try to omit step 6 but then all you DB objects will be owned by 
postgres. If this is what you need it is fine.

 

Hope this helps.

Bye

Charles

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alex Luya
Sent: Sonntag, 15. November 2015 14:44
To: Giuseppe Sacco 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL]

 

Ubuntu 14.04 64bit + postgresql 9.4 are used here;

 

What I have done are:

 

1,Sign In ubuntu by user alex

 

2,then:sudo -i -u postgres

 

3,then:psql jump into command client

 

4,then: create database icare;

 

5,then: \q jump out command client

 

6,then run: exit jump out psql

 

7,then pg_restore --clean --create --exit-on-error --dbname=icare icare-test.tar

 

 

On Fri, Nov 13, 2015 at 10:52 PM, Giuseppe Sacco 
mailto:giuse...@eppesuigoccas.homedns.org> 
> wrote:

Hello,


Il giorno ven, 13/11/2015 alle 13.38 +0800, Alex Luya ha scritto:
> Hello,
>I created a new database by 
>create database icare;
>then quit off psql and  run:
> pg_restore --clean --create --exit-on-error --dbname=icare
> icare-test.tar
>  it complains:
>   pg_restore: [archiver (db)] Error while PROCESSING TOC:
>   pg_restore: [archiver (db)] Error from TOC entry 21; 2615
> 80924 SCHEMA icare icare
>   pg_restore: [archiver (db)] could not execute query: ERROR:
>  permission denied for database icare
>Command was: CREATE SCHEMA icare;

>From what I understand, it means that the postgresql user that is
restoring the dump cannot create a schema on "icare" database. So, is
that user the same that issued che "create database" earlier? If it's
not, then you should grant all required priviledges to that user.

Bye,
Giuseppe



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

[Charles Clavadetscher]

Instead of creating a superuser you may simply grant the privilege on the 
database:

 

CREATE ROLE alex LOGIN PASSWORD '...';

GRANT CREATE ON DATABASE icare TO alex;

 

Bye

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles Clavadetscher
Sent: Sonntag, 15. November 2015 15:36
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL]

 

Hello

 

I never used pg_restore, but I assume that it works similarly to other PG 
tools, i.e. if you don’t specify a user to connect to the database it will take 
your unix user name. I assume that your step number 6 is the closing of the 
shell where you were as user postgres because after 5 you already are out of 
psql.

 

So the questions is, if you have a DB user alex with the Create privilege on 
the database icare.

 

If not you may try the following:

 

-Open a psql terminal (steps 1-3)

-CREATE ROLE alex LOGIN SUPERUSER PASSWORD '...';

 

And try the pg_restore again (steps 5-7).

 

You may also try to omit step 6 but then all you DB objects will be owned by 
postgres. If this is what you need it is fine.

 

Hope this helps.

Bye

Charles

 

 

From: pgsql-general-ow...@postgresql.org 
<mailto:pgsql-general-ow...@postgresql.org>  
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alex Luya
Sent: Sonntag, 15. November 2015 14:44
To: Giuseppe Sacco mailto:giuse...@eppesuigoccas.homedns.org> >
Cc: pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org> 
Subject: Re: [GENERAL]

 

Ubuntu 14.04 64bit + postgresql 9.4 are used here;

 

What I have done are:

 

1,Sign In ubuntu by user alex

 

2,then:sudo -i -u postgres

 

3,then:psql jump into command client

 

4,then: create database icare;

 

5,then: \q jump out command client

 

6,then run: exit jump out psql

 

7,then pg_restore --clean --create --exit-on-error --dbname=icare icare-test.tar

 

 

On Fri, Nov 13, 2015 at 10:52 PM, Giuseppe Sacco 
mailto:giuse...@eppesuigoccas.homedns.org> 
> wrote:

Hello,


Il giorno ven, 13/11/2015 alle 13.38 +0800, Alex Luya ha scritto:
> Hello,
>I created a new database by 
>create database icare;
>then quit off psql and  run:
> pg_restore --clean --create --exit-on-error --dbname=icare
> icare-test.tar
>  it complains:
>   pg_restore: [archiver (db)] Error while PROCESSING TOC:
>   pg_restore: [archiver (db)] Error from TOC entry 21; 2615
> 80924 SCHEMA icare icare
>   pg_restore: [archiver (db)] could not execute query: ERROR:
>  permission denied for database icare
>Command was: CREATE SCHEMA icare;

>From what I understand, it means that the postgresql user that is
restoring the dump cannot create a schema on "icare" database. So, is
that user the same that issued che "create database" earlier? If it's
not, then you should grant all required priviledges to that user.

Bye,
Giuseppe



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

Hello

 

Why don’t you simply change the port in postgresql.conf and restart the server?

 

Bye

Charles

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 09:58
To: pgsql-general 
Subject: [GENERAL] Transfer db from one port to another

 

I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and postgreSQL 
9.4 on port 5532 with the latter set up to use with Bitnami stack to test php 
files I am generating from my db. 

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

ERROR:  syntax error at or near "\"
LINE 26: \connect irll_project

with the lines in the .sql file created being

ALTER DATABASE irll_project OWNER TO postgres;

\connect irll_project


I have tried to use the Windows psql shell command to import using

\i 'C:/all.sql'

on pressing enter I see multiple occurrences saying invalid command, and then 
it ends with  

 

error: out of memory detail: failed on request of size 268435456

What is the correct command line?

Killian DriscoIl

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 
Cc: Andreas Kretschmer ; pgsql-general 

Subject: Re: [GENERAL] Transfer db from one port to another

 

On 23 December 2015 at 10:58, Charles Clavadetscher 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 mailto:pie...@hogranch.com> > 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]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 11:26
To: John R Pierce 
Cc: pgsql-general 
Subject: Re: [GENERAL] Transfer db from one port to another

 

 

On 23 December 2015 at 11:19, Killian Driscoll mailto:killiandrisc...@gmail.com> > wrote:

On 23 December 2015 at 11:07, John R Pierce mailto:pie...@hogranch.com> > 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 

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"

 

  Do you have a user killian in the database? If not you can either create 
it or use pg_dump with the –U switch to set it to the existing user (and with 
privileges on the database of course).


pg_restore: [archiver] input file is too short (read 0, expected 5)
 



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 mailto:adrian.kla...@aklaver.com> > wrote:

On 12/23/2015 03:43 AM, Killian Driscoll wrote:

On 23 December 2015 at 11:36, John R Pierce mailto:pie...@hogranch.com> 
 >> 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  

 

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] 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 mailto:rd0...@gmail.com> > 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] 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] 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 ix_inode_segments_notes_clientids
>

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 (pgsql-general@postgresql.org)
To make changes to you

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] 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 ; clavadetsc...@swisspug.org
> Cc: Postgres General 
> Subject: Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2
> 
> On 1/24/16, Christophe Pettus  wrote:
> >
> > On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher
> >  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: 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] 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] 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' ; 'PostgreSQL General' 
> 
> 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

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