Re: [GENERAL] Asp.net 5 and EF6

[John R Pierce]

On 2/4/2016 2:34 PM, FarjadFarid(ChkNet) wrote:

It looks like builds versioning number but not sure if
anyone has tried asp.net 5 or not?



I personally don't use ASP.NET anything.  The people I know who do 
develop software for .NET generally try and stay with the lowest common 
denominator, so are mostly still at framework 3.5, so their software 
runs on as many platforms as possible.   Enterprise deployments are 
generally very leary of bleeding edge software.


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

Re: [GENERAL] Asp.net 5 and EF6

[FarjadFarid(ChkNet)]

It looks like builds versioning number but not sure if 
anyone has tried asp.net 5 or not? 

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: 04 February 2016 18:02
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Asp.net 5 and EF6

On 2/4/2016 9:52 AM, FarjadFarid(ChkNet) wrote:
> I am not using EF7. It is actually EF6 under full .net 5

then why is the error...

> NU1007 Dependency specified was EntityFramework7.Npgsql >=1.0.0 but 
> ended with EntityFramework7.Npgsql 1.0.0

??


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

Re: [GENERAL] Fatal error when not numeric value - PostgreSQL 9.2

[Adrian Klaver]

On 02/04/2016 01:35 PM, drum.lu...@gmail.com wrote:


On 4 February 2016 at 12:03, David G. Johnston
mailto:david.g.johns...@gmail.com>
>> wrote:

 On Wed, Feb 3, 2016 at 3:48 PM, drum.lu...@gmail.com

 >
mailto:drum.lu...@gmail.com>
 >>wrote:

 Hi all,

 Below is an example of the auto-generated update query,
with
 client-supplied keys (_iid). There's a fatal error when
_iid is
 not numeric. However; this should accept any value.

 *Question:* How could I do something that would allow
_iid to be
 more than just an INT?

 |WITHin_rows AS(SELECTCAST(customer_id
ASBIGINT),
  csv_data,freshbooks_id,myob_id,
 ppy_id,qb_id,xero_id,_iid


FROM(VALUES('3905',E'\x1A',E'\x1A','c59894c-142b6',E'\x1A',E'\x1A',E'\x1A','44'),('39107',E'\x1A',E'\x1A','6260-2ba1',E'\x1A',E'\x1A',E'\x1A','65e-0f0d-49b4-9ac1-a8752ba1'),|

 |Thank you|
 |Lucas|


 ​You have a fatal error because the query you provided is
 malformed.  Send something that works, and provokes the
relevant
 error, and we might be able to help.

 David J.
 ​


QUERY:

|WITHin_rows AS(SELECTCAST(customer_id
ASBIGINT),csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid

FROM(VALUES('3915105',E'\x1A',E'\x1A','c59894cb-0ffe-4ad6-823d-73c1392142b6',E'\x1A',E'\x1A',E'\x1A','44'),('3915135',E'\x1A',E'\x1A','fe88ff8f-6b4d-4e3d-8020-3475a101d25e',E'\x1A',E'\x1A',E'\x1A','fe88ff8f-6b4d-4e3d-8020-3475a101d25e'),)ASid(customer_id,csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid)),id_overlays
AS(SELECTir.customer_id,(tt.customer_id
ISNOTNULL)AStt_matched,(CASEWHEN(ir.csv_data ::TEXT
=E'\x1A')THENtt.csv_data ::TEXT ELSENULLIF(ir.csv_data
::TEXT,E'\x18')END)AScsv_data,(CASEWHEN(ir.freshbooks_id ::TEXT
=E'\x1A')THENtt.freshbooks_id ::TEXT ELSENULLIF(ir.freshbooks_id
::TEXT,E'\x18')END)ASfreshbooks_id,(CASEWHEN(ir.myob_id ::TEXT
=E'\x1A')THENtt.myob_id ::TEXT ELSENULLIF(ir.myob_id
::TEXT,E'\x18')END)ASmyob_id,(CASEWHEN(ir.ppy_id ::TEXT
=E'\x1A')THENtt.ppy_id ::TEXT ELSENULLIF(ir.ppy_id
::TEXT,E'\x18')END)ASppy_id,(CASEWHEN(ir.qb_id ::TEXT
=E'\x1A')THENtt.qb_id ::TEXT ELSENULLIF(ir.qb_id
::TEXT,E'\x18')END)ASqb_id,(CASEWHEN(ir.xero_id ::TEXT
=E'\x1A')THENtt.xero_id ::TEXT ELSENULLIF(ir.xero_id
::TEXT,E'\x18')END)ASxero_id,ir._iid ::TEXT AS_iid FROMin_rows ASir


Well above you are turning _iid into ::TEXT which is fine, though(as
David pointed out) the receiving field should be that type also. So
the problem is not your casting of _iid it is where you are sending
that value.

LEFTJOINintegrations.customers AStt

USING(customer_id))SELECTio.customer_id,io._iid,io.tt_matched,((io.csv_data
ISNOTNULL)OR(io.freshbooks_id ISNOTNULL)OR(io.myob_id
ISNOTNULL)OR(io.ppy_id ISNOTNULL)OR(io.qb_id ISNOTNULL)OR(io.xero_id
ISNOTNULL))AStt_stays FROMid_overlays ASio;|

ERROR:

|ERROR:invalid input syntax
forinteger:"fe88ff8f-6b4d-4e3d-8020-3475a101d25e"at character 419|






I believe that's what culling out the record from being
inserted/updated, it's the prepass before safedatainjector. There is no
update persay. It selects records that it thinks it needs to either
insert/update/delete defined by tt_matched with some php logic that then
splits the collections.


So at some point a database table is being INSERTed or UPDATEd into and 
given this:


test=> \d tbl_a
 Table "public.tbl_a"
 Column |  Type   | Modifiers
+-+---
 fld_1  | integer |


test=> insert into tbl_a values ('fe88ff8f-6b4d-4e3d-8020-3475a101d25e');
ERROR:  invalid input syntax for integer: 
"fe88ff8f-6b4d-4e3d-8020-3475a101d25e"


I would say the code is trying to put a non-integer value into a table 
field that is an integer. So you need to look at the table that is being 
operated on and see if the PHP code is correctly matching the fields. 
When I have run into this it is because of an indexing issue, usually 
caused by either a change to the table schema or a one off error in the 
indexing code.



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


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

Re: [GENERAL] Fatal error when not numeric value - PostgreSQL 9.2

[drum.lu...@gmail.com]

>
>
>> On 4 February 2016 at 12:03, David G. Johnston
>> mailto:david.g.johns...@gmail.com>> wrote:
>>
>> On Wed, Feb 3, 2016 at 3:48 PM, drum.lu...@gmail.com
>>  > >wrote:
>>
>> Hi all,
>>
>> Below is an example of the auto-generated update query, with
>> client-supplied keys (_iid). There's a fatal error when _iid is
>> not numeric. However; this should accept any value.
>>
>> *Question:* How could I do something that would allow _iid to be
>> more than just an INT?
>>
>> |WITHin_rows AS(SELECTCAST(customer_id ASBIGINT),
>>  csv_data,freshbooks_id,myob_id,
>> ppy_id,qb_id,xero_id,_iid
>>
>> FROM(VALUES('3905',E'\x1A',E'\x1A','c59894c-142b6',E'\x1A',E'\x1A',E'\x1A','44'),('39107',E'\x1A',E'\x1A','6260-2ba1',E'\x1A',E'\x1A',E'\x1A','65e-0f0d-49b4-9ac1-a8752ba1'),|
>>
>> |Thank you|
>> |Lucas|
>>
>>
>> ​You have a fatal error because the query you provided is
>> malformed.  Send something that works, and provokes the relevant
>> error, and we might be able to help.
>>
>> David J.
>> ​
>>
>>
>> QUERY:
>>
>> |WITHin_rows AS(SELECTCAST(customer_id
>> ASBIGINT),csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid
>>
>> FROM(VALUES('3915105',E'\x1A',E'\x1A','c59894cb-0ffe-4ad6-823d-73c1392142b6',E'\x1A',E'\x1A',E'\x1A','44'),('3915135',E'\x1A',E'\x1A','fe88ff8f-6b4d-4e3d-8020-3475a101d25e',E'\x1A',E'\x1A',E'\x1A','fe88ff8f-6b4d-4e3d-8020-3475a101d25e'),)ASid(customer_id,csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid)),id_overlays
>> AS(SELECTir.customer_id,(tt.customer_id
>> ISNOTNULL)AStt_matched,(CASEWHEN(ir.csv_data ::TEXT
>> =E'\x1A')THENtt.csv_data ::TEXT ELSENULLIF(ir.csv_data
>> ::TEXT,E'\x18')END)AScsv_data,(CASEWHEN(ir.freshbooks_id ::TEXT
>> =E'\x1A')THENtt.freshbooks_id ::TEXT ELSENULLIF(ir.freshbooks_id
>> ::TEXT,E'\x18')END)ASfreshbooks_id,(CASEWHEN(ir.myob_id ::TEXT
>> =E'\x1A')THENtt.myob_id ::TEXT ELSENULLIF(ir.myob_id
>> ::TEXT,E'\x18')END)ASmyob_id,(CASEWHEN(ir.ppy_id ::TEXT
>> =E'\x1A')THENtt.ppy_id ::TEXT ELSENULLIF(ir.ppy_id
>> ::TEXT,E'\x18')END)ASppy_id,(CASEWHEN(ir.qb_id ::TEXT
>> =E'\x1A')THENtt.qb_id ::TEXT ELSENULLIF(ir.qb_id
>> ::TEXT,E'\x18')END)ASqb_id,(CASEWHEN(ir.xero_id ::TEXT
>> =E'\x1A')THENtt.xero_id ::TEXT ELSENULLIF(ir.xero_id
>> ::TEXT,E'\x18')END)ASxero_id,ir._iid ::TEXT AS_iid FROMin_rows ASir
>>
>
> Well above you are turning _iid into ::TEXT which is fine, though(as David
> pointed out) the receiving field should be that type also. So the problem
> is not your casting of _iid it is where you are sending that value.
>
> LEFTJOINintegrations.customers AStt
>>
>> USING(customer_id))SELECTio.customer_id,io._iid,io.tt_matched,((io.csv_data
>> ISNOTNULL)OR(io.freshbooks_id ISNOTNULL)OR(io.myob_id
>> ISNOTNULL)OR(io.ppy_id ISNOTNULL)OR(io.qb_id ISNOTNULL)OR(io.xero_id
>> ISNOTNULL))AStt_stays FROMid_overlays ASio;|
>>
>> ERROR:
>>
>> |ERROR:invalid input syntax
>> forinteger:"fe88ff8f-6b4d-4e3d-8020-3475a101d25e"at character 419|
>> 
>>
>

I believe that's what culling out the record from being inserted/updated,
it's the prepass before safedatainjector. There is no update persay. It
selects records that it thinks it needs to either insert/update/delete
defined by tt_matched with some php logic that then splits the collections.

Re: [GENERAL] Cannot Create Objects

[Adrian Klaver]

On 02/04/2016 11:42 AM, Alex Magnum wrote:

Hi,
I am having a few problems with access permissions.

When I create a new role with NOCREATEUSER and then create a database
for that role I can connect to the DB but when trying to create a db
object I will get the ERROR:  permission denied for schema public.

Strangely though, if the role is created with CREATEUSERS I don't have
any problems.

Here is what I want to do:

 1. Create a DBO role e.g. dbo_xxx NOCREATEDB NOCREATEUSER
 2. Create a db   mydb WITH OWNER db_xxx
 3. REVOKE all connection rights from public
 4. GRANT only rights to dbo_xxx
 5. GRANT all create rights on mydb TO dbo_xxx ; allowing the user to
load the db schema

This is what I tried
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE USER dbo_xxx WITH PASSWORD 'mypass' NOCREATEDB NOCREATEUSER;


Just realized I should have asked where the above took place?



CREATE DATABASE my_db WITH OWNER dbo_xxx ENCODING 'UTF8';
REVOKE CONNECT ON DATABASE my_db FROM PUBLIC;
GRANT CONNECT ON DATABASE my_db TO dbo_xxx;
GRANT ALL PRIVILEGES ON DATABASE my_db TO dbo_xxx;
-- After schema is loaded
CREATE USER read_only WITH PASSWORD 'mypass' NOCREATEDB NOCREATEUSER;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only ;

But i end up with permission denied errors.

Anyone having a suggestion how to get this to work? Did I mess up
permissions in public schema?

Any help and suggestion is greatly appreciated.

Alex





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


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

Re: [GENERAL] Cannot Create Objects

[Adrian Klaver]

On 02/04/2016 11:42 AM, Alex Magnum wrote:

Hi,
I am having a few problems with access permissions.

When I create a new role with NOCREATEUSER and then create a database
for that role I can connect to the DB but when trying to create a db
object I will get the ERROR:  permission denied for schema public.

Strangely though, if the role is created with CREATEUSERS I don't have
any problems.


So what arguments do you give to createuser and what does it show when 
you add -e to the command?




Here is what I want to do:

 1. Create a DBO role e.g. dbo_xxx NOCREATEDB NOCREATEUSER
 2. Create a db   mydb WITH OWNER db_xxx
 3. REVOKE all connection rights from public
 4. GRANT only rights to dbo_xxx
 5. GRANT all create rights on mydb TO dbo_xxx ; allowing the user to
load the db schema

This is what I tried


Who are doing the below as?


REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE USER dbo_xxx WITH PASSWORD 'mypass' NOCREATEDB NOCREATEUSER;

CREATE DATABASE my_db WITH OWNER dbo_xxx ENCODING 'UTF8';
REVOKE CONNECT ON DATABASE my_db FROM PUBLIC;
GRANT CONNECT ON DATABASE my_db TO dbo_xxx;
GRANT ALL PRIVILEGES ON DATABASE my_db TO dbo_xxx;


Well the above only GRANTs on the database not objects within it. For 
more information see:


http://www.postgresql.org/docs/9.4/interactive/sql-grant.html

For databases that means CREATE and CONNECT

Since you already REVOKed ALL on schema public FROM PUBLIC and did not 
GRANT SCHEMA privileges to dbo_xxx on schema public, I am pretty sure 
that is where your problem is. To get a clearer idea of what is going on 
can you show:


\l my_db

and in my_db

\dn+ public



-- After schema is loaded
CREATE USER read_only WITH PASSWORD 'mypass' NOCREATEDB NOCREATEUSER;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only ;

But i end up with permission denied errors.

Anyone having a suggestion how to get this to work? Did I mess up
permissions in public schema?

Any help and suggestion is greatly appreciated.

Alex





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


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

Re: [GENERAL] Recursive CTE in function problem

[Doug Kyle]

ugh, simple syntax screw up.  Thanks David.

On 02/04/2016 02:45 PM, David G. Johnston wrote:
On Thu, Feb 4, 2016 at 12:37 PM, Doug Kyle >wrote:


But when I use it as a function it always returns false:

CREATE OR REPLACE FUNCTION
grpl_collection.copy_in_collection_name(cpid bigint, colname text)
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
BEGIN
RETURN cpid in (select copy from
grpl_collection.collections_copy_map where copy=cpid and collection in
(WITHRECURSIVE
q AS
(
SELECT c.id 
FROMgrpl_collection.collections c
WHERE   name=$$colname$$
​  <<<​

UNION
SELECT cn.id 
FROMq
JOINgrpl_collection.collections cn
ON  cn.parent = q.id 
)
SELECT id FROM q ));
END;
$function$


​ $$colname$$ is a string whose contents is the literal 'colname'​, 
not the function argument named colname as you seem to want.


David J.

Re: [GENERAL] Recursive CTE in function problem

[David G. Johnston]

On Thu, Feb 4, 2016 at 12:37 PM, Doug Kyle  wrote:

> But when I use it as a function it always returns false:
>
> CREATE OR REPLACE FUNCTION grpl_collection.copy_in_collection_name(cpid
> bigint, colname text)
>  RETURNS boolean
>  LANGUAGE plpgsql
> AS $function$
> BEGIN
> RETURN cpid in (select copy from grpl_collection.collections_copy_map
> where copy=cpid and collection in
> (WITHRECURSIVE
> q AS
> (
> SELECT  c.id
> FROMgrpl_collection.collections c
> WHERE   name=$$colname$$
> ​  <<<​
>
> UNION
> SELECT  cn.id
> FROMq
> JOINgrpl_collection.collections cn
> ON  cn.parent = q.id
> )
> SELECT id FROM q ));
> END;
> $function$


​$$colname$$ is a string whose contents is the literal 'colname'​, not the
function argument named colname as you seem to want.

David J.

[GENERAL] Cannot Create Objects

[Alex Magnum]

Hi,
I am having a few problems with access permissions.

When I create a new role with NOCREATEUSER and then create a database for
that role I can connect to the DB but when trying to create a db object I
will get the ERROR:  permission denied for schema public.

Strangely though, if the role is created with CREATEUSERS I don't have any
problems.

Here is what I want to do:

   1. Create a DBO role e.g. dbo_xxx NOCREATEDB NOCREATEUSER
   2. Create a db   mydb WITH OWNER db_xxx
   3. REVOKE all connection rights from public
   4. GRANT only rights to dbo_xxx
   5. GRANT all create rights on mydb TO dbo_xxx ; allowing the user to
   load the db schema

This is what I tried
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE USER dbo_xxx WITH PASSWORD 'mypass' NOCREATEDB NOCREATEUSER;

CREATE DATABASE my_db WITH OWNER dbo_xxx ENCODING 'UTF8';
REVOKE CONNECT ON DATABASE my_db FROM PUBLIC;
GRANT CONNECT ON DATABASE my_db TO dbo_xxx;
GRANT ALL PRIVILEGES ON DATABASE my_db TO dbo_xxx;
-- After schema is loaded
CREATE USER read_only WITH PASSWORD 'mypass' NOCREATEDB NOCREATEUSER;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only ;

But i end up with permission denied errors.

Anyone having a suggestion how to get this to work? Did I mess up
permissions in public schema?

Any help and suggestion is greatly appreciated.

Alex

[GENERAL] Recursive CTE in function problem

[Doug Kyle]

Postgres 9.4.1, with these tables:

grpl_collection.collections
 id | integer |
 name   | text|
 parent | bigint  |
(An adjacency list representing a collections hierarchy)

grpl_collection.collections_copy_map
 id | integer |
 collection | bigint  |
 copy   | bigint  |
(map a copy to the collections it belongs to)

This query correctly answers if a copy is in a collection tree:

 select 3006377 in (select copy from 
grpl_collection.collections_copy_map where copy=3006377 and collection in

(WITHRECURSIVE
q AS
(
SELECT  c.id
FROMgrpl_collection.collections c
WHERE   name=$$Movies$$
UNION ALL
SELECT  cn.id
FROMq
JOINgrpl_collection.collections cn
ON  cn.parent = q.id
)
SELECT  id
FROMq));


But when I use it as a function it always returns false:

CREATE OR REPLACE FUNCTION grpl_collection.copy_in_collection_name(cpid 
bigint, colname text)

 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
BEGIN
RETURN cpid in (select copy from 
grpl_collection.collections_copy_map where copy=cpid and collection in

(WITHRECURSIVE
q AS
(
SELECT  c.id
FROMgrpl_collection.collections c
WHERE   name=$$colname$$
UNION
SELECT  cn.id
FROMq
JOINgrpl_collection.collections cn
ON  cn.parent = q.id
)
SELECT id FROM q ));
END;
$function$

What am I missing?


--
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] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

[Karl Denninger]

On 2/4/2016 12:47, Tom Lane wrote:
> I wrote:
>> Karl Denninger  writes:
>>> $ initdb -D data-default
>>> ...
>>> creating template1 database in data-default/base/1 ... FATAL:  could not
>>> create semaphores: Invalid argument
>>> DETAIL:  Failed system call was semget(2, 17, 03600).
>> Hmm.  On my Linux box, "man semget" says EINVAL means
>>EINVAL nsems  is less than 0 or greater than the limit on the number 
>> of
>>   semaphores per semaphore set (SEMMSL), or a semaphore set 
>> corre-
>>   sponding  to  key  already  exists, and nsems is larger than 
>> the
>>   number of semaphores in that set.
>> which agrees with the POSIX spec.  Is FreeBSD the same?
> BTW, looking at the code, I see that during initdb we would have tried
> semaphore key 1 before 2.  So presumably, on key 1 we got an error code
> that we recognized as meaning "semaphore set already exists", but then on
> key 2 we got EINVAL instead.  That makes this even more curious.  I'd
> be interested to see what "ipcs -s" says, if you have that command.
> (You might need to run it as root to be sure it will show all sempaphores.)
>
>   regards, tom lane
There was indeed a "2" key out by the web server process; I shut it down
and cleared it and the upgrade is now running

Also filed a kernel bug with the FreeBSD folks against 10.2-STABLE as
the man page says you should have gotten back EEXIST.


-- 
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

[Tom Lane]

I wrote:
> Karl Denninger  writes:
>> $ initdb -D data-default
>> ...
>> creating template1 database in data-default/base/1 ... FATAL:  could not
>> create semaphores: Invalid argument
>> DETAIL:  Failed system call was semget(2, 17, 03600).

> Hmm.  On my Linux box, "man semget" says EINVAL means

>EINVAL nsems  is less than 0 or greater than the limit on the number of
>   semaphores per semaphore set (SEMMSL), or a semaphore set corre-
>   sponding  to  key  already  exists, and nsems is larger than the
>   number of semaphores in that set.

> which agrees with the POSIX spec.  Is FreeBSD the same?

BTW, looking at the code, I see that during initdb we would have tried
semaphore key 1 before 2.  So presumably, on key 1 we got an error code
that we recognized as meaning "semaphore set already exists", but then on
key 2 we got EINVAL instead.  That makes this even more curious.  I'd
be interested to see what "ipcs -s" says, if you have that command.
(You might need to run it as root to be sure it will show all sempaphores.)

regards, tom lane


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

Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

[Karl Denninger]

On 2/4/2016 12:28, Tom Lane wrote:
> Karl Denninger  writes:
>> $ initdb -D data-default
>> ...
>> creating template1 database in data-default/base/1 ... FATAL:  could not
>> create semaphores: Invalid argument
>> DETAIL:  Failed system call was semget(2, 17, 03600).
> Hmm.  On my Linux box, "man semget" says EINVAL means
>
>EINVAL nsems  is less than 0 or greater than the limit on the number of
>   semaphores per semaphore set (SEMMSL), or a semaphore set corre-
>   sponding  to  key  already  exists, and nsems is larger than the
>   number of semaphores in that set.
>
> which agrees with the POSIX spec.  Is FreeBSD the same?
>
> Proceeding on the assumption that it is ...
>
> 17 is the same nsems value we've been using for donkey's years, so the
> SEMMSL aspect of this seems unlikely to apply; what presumably is
> happening is a collision with an existing semaphore's key.  Our code is
> prepared for that, but it expects a different error code in such cases,
> either EEXIST or EACCES:
>
> /*
>  * Fail quietly if error indicates a collision with existing set. One
>  * would expect EEXIST, given that we said IPC_EXCL, but perhaps we
>  * could get a permission violation instead?  Also, EIDRM might occur
>  * if an old set is slated for destruction but not gone yet.
>  */
>
> It sounds like your kernel is returning EINVAL in preference to any of
> those codes, which would be pretty broken.  I do not want to make our code
> treat EINVAL as meaning we should retry with a different key, because if
> the problem is indeed the SEMMSL limit, we'd be in an infinite loop.
>
> You can probably get past this for the moment if you can remove the
> semaphore set with key 2, but I'd advise filing a FreeBSD kernel
> bug about their choice of errno.
>
>   regards, tom lane

That sounds like it well may be the problem

s655642 --rw-rw-rw- www  www  www 
www 3 12:29:14  7:56:04


Oh look, the web server process has a semaphore set out with a "2" key

I've filed a bug report.  Thanks.

-- 
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

[Tom Lane]

Karl Denninger  writes:
> $ initdb -D data-default
> ...
> creating template1 database in data-default/base/1 ... FATAL:  could not
> create semaphores: Invalid argument
> DETAIL:  Failed system call was semget(2, 17, 03600).

Hmm.  On my Linux box, "man semget" says EINVAL means

   EINVAL nsems  is less than 0 or greater than the limit on the number of
  semaphores per semaphore set (SEMMSL), or a semaphore set corre-
  sponding  to  key  already  exists, and nsems is larger than the
  number of semaphores in that set.

which agrees with the POSIX spec.  Is FreeBSD the same?

Proceeding on the assumption that it is ...

17 is the same nsems value we've been using for donkey's years, so the
SEMMSL aspect of this seems unlikely to apply; what presumably is
happening is a collision with an existing semaphore's key.  Our code is
prepared for that, but it expects a different error code in such cases,
either EEXIST or EACCES:

/*
 * Fail quietly if error indicates a collision with existing set. One
 * would expect EEXIST, given that we said IPC_EXCL, but perhaps we
 * could get a permission violation instead?  Also, EIDRM might occur
 * if an old set is slated for destruction but not gone yet.
 */

It sounds like your kernel is returning EINVAL in preference to any of
those codes, which would be pretty broken.  I do not want to make our code
treat EINVAL as meaning we should retry with a different key, because if
the problem is indeed the SEMMSL limit, we'd be in an infinite loop.

You can probably get past this for the moment if you can remove the
semaphore set with key 2, but I'd advise filing a FreeBSD kernel
bug about their choice of errno.

regards, tom lane


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

Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

[Adrian Klaver]

On 02/04/2016 10:02 AM, Karl Denninger wrote:

$ initdb -D data-default
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory data-default ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in data-default/base/1 ... FATAL:  could not
create semaphores: Invalid argument
DETAIL:  Failed system call was semget(2, 17, 03600).
child process exited with exit code 1
initdb: removing contents of data directory "data-default"
$
$ sysctl -a|grep semm
kern.ipc.semmsl: 512
kern.ipc.semmnu: 256
kern.ipc.semmns: 512
kern.ipc.semmni: 256

The system is running 9.4 just fine and the kernel configuration
requirements shouldn't have changed for semaphores should they?


If it helps, the hint from the source code:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/port/sysv_sema.c;h=f6f15169200a03e9da46ae348994f04297d22017;hb=HEAD

"This error does *not* mean that you have run out of disk space. It 
occurs when either the system limit for the maximum number of semaphore 
sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), 
would be exceeded.  You need to raise the respective kernel parameter. 
Alternatively, reduce PostgreSQL's consumption of semaphores by reducing 
its max_connections parameter.
The PostgreSQL documentation contains more information about configuring 
your system for PostgreSQL."





--
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/



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


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

Re: [GENERAL] Asp.net 5 and EF6

[Adrian Klaver]

On 02/04/2016 09:52 AM, FarjadFarid(ChkNet) wrote:


I am not using EF7. It is actually EF6 under full .net 5


Your previous post seemed to be referring to these instructions:

http://www.npgsql.org/doc/ef7.html

which would indicate you are using EF7, and as John pointed out, so does 
the error message.


FYI there is an issue that is tracking EF7 support:

https://github.com/npgsql/npgsql/issues/249




-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: 04 February 2016 17:29
To: FarjadFarid(ChkNet); pgsql-general@postgresql.org
Subject: Re: [GENERAL] Asp.net 5 and EF6

On 02/04/2016 09:18 AM, FarjadFarid(ChkNet) wrote:

I have looked the Npgsql website and downloaded the EF7 files. Of
course the full .net core.


Alright now I am not following, in your original post you said:

"Hi I am trying to use certain features of Asp.net 5 using postgresql using
EF6."

and

"So I would have thought as an interim measure until EF7 is ready which will
be quite some time to come, it should be possible to connect to postgresql
using EF6. "

So why are you using the EF7 files?



The problem *seems* to be that there are two separate libraries that
need to be added.

One is EF related and the other is the Npgsql wrapper.
Unfortunately the Nuget installer is not working correctly either.

The main error seems to be in the submitted version of the libraries
and build version.

The error is
NU1007 Dependency specified was EntityFramework7.Npgsql >=1.0.0 but
ended with EntityFramework7.Npgsql 1.0.0

Npgsql namespace is installed but not the entity framework.

It looks as if EF7 will be released much later than asp.net 5. So a
lot of time could be lost in the middle.

Current suggested workaround for all DB's solution is to use EF6 under
a separate .net project.

Any suggestion?

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: 04 February 2016 17:00
To: FarjadFarid(ChkNet); pgsql-general@postgresql.org
Subject: Re: [GENERAL] Asp.net 5 and EF6

On 02/04/2016 08:46 AM, FarjadFarid(ChkNet) wrote:

Hi I am trying to use certain features of Asp.net 5 using postgresql
using EF6.

I have downloaded the latest unstable version of npgsql and tried to
connect to my db.

But no luck.

I am not a nppsql user, so some generic question.

Can you explain in more detail what the errors are?



I have posted a request to stackoverflow.com but haven't had any
response at all.

It is easy to connect to Sql Server using EF6 under asp.net 5.

So I would have thought as an interim measure until EF7 is ready
which will be quite some time to come,

it should be possible to connect to postgresql using EF6.


Have you looked here?:

http://www.npgsql.org/doc/ef6.html



Any ideas how?

Many thanks.




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





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





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


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

Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

[Adrian Klaver]

On 02/04/2016 10:02 AM, Karl Denninger wrote:

$ initdb -D data-default
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory data-default ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in data-default/base/1 ... FATAL:  could not
create semaphores: Invalid argument
DETAIL:  Failed system call was semget(2, 17, 03600).
child process exited with exit code 1
initdb: removing contents of data directory "data-default"
$
$ sysctl -a|grep semm
kern.ipc.semmsl: 512
kern.ipc.semmnu: 256
kern.ipc.semmns: 512
kern.ipc.semmni: 256

The system is running 9.4 just fine and the kernel configuration
requirements shouldn't have changed for semaphores should they?



Where did the 9.5 version come from and was it the same source as the 
9.4 version?




--
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/



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


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

[GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb

[Karl Denninger]

$ initdb -D data-default
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory data-default ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in data-default/base/1 ... FATAL:  could not
create semaphores: Invalid argument
DETAIL:  Failed system call was semget(2, 17, 03600).
child process exited with exit code 1
initdb: removing contents of data directory "data-default"
$
$ sysctl -a|grep semm
kern.ipc.semmsl: 512
kern.ipc.semmnu: 256
kern.ipc.semmns: 512
kern.ipc.semmni: 256

The system is running 9.4 just fine and the kernel configuration
requirements shouldn't have changed for semaphores should they?

-- 
Karl Denninger
k...@denninger.net 
/The Market Ticker/
/[S/MIME encrypted email preferred]/


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Asp.net 5 and EF6

[John R Pierce]

On 2/4/2016 9:52 AM, FarjadFarid(ChkNet) wrote:

I am not using EF7. It is actually EF6 under full .net 5


then why is the error...


NU1007 Dependency specified was EntityFramework7.Npgsql >=1.0.0 but
ended with EntityFramework7.Npgsql 1.0.0


??


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

Re: [GENERAL] [SQL] Q: documentation bug ?

[Fabrízio de Royes Mello]

On 04-02-2016 08:53, Karsten Hilbert wrote:
> Hello all,
> 
> the online documentation for REINDEX
> 
>   http://www.postgresql.org/docs/9.5/static/sql-reindex.html
> 
> talks about VERBOSE
> 
>   Synopsis
> 
>   REINDEX [ ( { VERBOSE } [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE 
> | SYSTEM } name
> 
>   [...]
> 
>   VERBOSE
> 
>   Prints a progress report as each index is reindexed.
> 
> but
> 
>   root@hermes:~/bin# psql -d gnumed_v21 -U gm-dbo
>   Ausgabeformat ist „wrapped“.
>   Erweiterte Anzeige ist an.
>   psql (9.5.0)
>   Geben Sie „help“ für Hilfe ein.
> 
>   gnumed_v21=> select version();
>   -[ RECORD 1 
> ]---
>   version | PostgreSQL 9.5.0 on i686-pc-linux-gnu, compiled by gcc 
> (Debian 5.3.1-5) 5.3.1 20160101, 32-bit
> 
>   gnumed_v21=> reindex
>   DATABASE  INDEX SCHEMASYSTEMTABLE
> 
>   gnumed_v21=> reindex verbose database 'gnumed_v21';
>   ERROR:  syntax error at or near "verbose"
>   ZEILE 1: reindex verbose database 'gnumed_v21';
>^
>   gnumed_v21=>
> 
> Does this constitute a bug in the documentation or in the Debian version of 
> PG ?
> 

You forgot to add the "(" and ")" around the "VERBOSE" statement
acoording the syntax.

Try this:

REINDEX (VERBOSE) DATABASE gnumed_v21;

Regards,

-- 
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Asp.net 5 and EF6

[FarjadFarid(ChkNet)]

I am not using EF7. It is actually EF6 under full .net 5


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: 04 February 2016 17:29
To: FarjadFarid(ChkNet); pgsql-general@postgresql.org
Subject: Re: [GENERAL] Asp.net 5 and EF6

On 02/04/2016 09:18 AM, FarjadFarid(ChkNet) wrote:
> I have looked the Npgsql website and downloaded the EF7 files. Of 
> course the full .net core.

Alright now I am not following, in your original post you said:

"Hi I am trying to use certain features of Asp.net 5 using postgresql using
EF6."

and

"So I would have thought as an interim measure until EF7 is ready which will
be quite some time to come, it should be possible to connect to postgresql
using EF6. "

So why are you using the EF7 files?

>
> The problem *seems* to be that there are two separate libraries that 
> need to be added.
>
> One is EF related and the other is the Npgsql wrapper.
> Unfortunately the Nuget installer is not working correctly either.
>
> The main error seems to be in the submitted version of the libraries 
> and build version.
>
> The error is
> NU1007 Dependency specified was EntityFramework7.Npgsql >=1.0.0 but 
> ended with EntityFramework7.Npgsql 1.0.0
>
> Npgsql namespace is installed but not the entity framework.
>
> It looks as if EF7 will be released much later than asp.net 5. So a 
> lot of time could be lost in the middle.
>
> Current suggested workaround for all DB's solution is to use EF6 under 
> a separate .net project.
>
> Any suggestion?
>
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: 04 February 2016 17:00
> To: FarjadFarid(ChkNet); pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Asp.net 5 and EF6
>
> On 02/04/2016 08:46 AM, FarjadFarid(ChkNet) wrote:
>> Hi I am trying to use certain features of Asp.net 5 using postgresql 
>> using EF6.
>>
>> I have downloaded the latest unstable version of npgsql and tried to 
>> connect to my db.
>>
>> But no luck.
> I am not a nppsql user, so some generic question.
>
> Can you explain in more detail what the errors are?
>
>>
>> I have posted a request to stackoverflow.com but haven't had any 
>> response at all.
>>
>> It is easy to connect to Sql Server using EF6 under asp.net 5.
>>
>> So I would have thought as an interim measure until EF7 is ready 
>> which will be quite some time to come,
>>
>> it should be possible to connect to postgresql using EF6.
>
> Have you looked here?:
>
> http://www.npgsql.org/doc/ef6.html
>
>>
>> Any ideas how?
>>
>> Many thanks.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>


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



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

Re: [GENERAL] Asp.net 5 and EF6

[Adrian Klaver]

On 02/04/2016 09:18 AM, FarjadFarid(ChkNet) wrote:

I have looked the Npgsql website and downloaded the EF7 files. Of course the
full .net core.


Alright now I am not following, in your original post you said:

"Hi I am trying to use certain features of Asp.net 5 using postgresql 
using EF6."


and

"So I would have thought as an interim measure until EF7 is ready which 
will be quite some time to come, it should be possible to connect to 
postgresql using EF6. "


So why are you using the EF7 files?



The problem *seems* to be that there are two separate libraries that need to
be added.

One is EF related and the other is the Npgsql wrapper.
Unfortunately the Nuget installer is not working correctly either.

The main error seems to be in the submitted version of the libraries and
build version.

The error is
NU1007 Dependency specified was EntityFramework7.Npgsql >=1.0.0 but ended
with EntityFramework7.Npgsql 1.0.0

Npgsql namespace is installed but not the entity framework.

It looks as if EF7 will be released much later than asp.net 5. So a lot of
time could be lost in the middle.

Current suggested workaround for all DB's solution is to use EF6 under a
separate .net project.

Any suggestion?

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: 04 February 2016 17:00
To: FarjadFarid(ChkNet); pgsql-general@postgresql.org
Subject: Re: [GENERAL] Asp.net 5 and EF6

On 02/04/2016 08:46 AM, FarjadFarid(ChkNet) wrote:

Hi I am trying to use certain features of Asp.net 5 using postgresql
using EF6.

I have downloaded the latest unstable version of npgsql and tried to
connect to my db.

But no luck.

I am not a nppsql user, so some generic question.

Can you explain in more detail what the errors are?



I have posted a request to stackoverflow.com but haven't had any
response at all.

It is easy to connect to Sql Server using EF6 under asp.net 5.

So I would have thought as an interim measure until EF7 is ready which
will be quite some time to come,

it should be possible to connect to postgresql using EF6.


Have you looked here?:

http://www.npgsql.org/doc/ef6.html



Any ideas how?

Many thanks.




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





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


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

Re: [GENERAL] Asp.net 5 and EF6

[FarjadFarid(ChkNet)]

I have looked the Npgsql website and downloaded the EF7 files. Of course the
full .net core. 

The problem *seems* to be that there are two separate libraries that need to
be added.

One is EF related and the other is the Npgsql wrapper. 
Unfortunately the Nuget installer is not working correctly either. 

The main error seems to be in the submitted version of the libraries and
build version. 

The error is
NU1007 Dependency specified was EntityFramework7.Npgsql >=1.0.0 but ended
with EntityFramework7.Npgsql 1.0.0

Npgsql namespace is installed but not the entity framework.

It looks as if EF7 will be released much later than asp.net 5. So a lot of
time could be lost in the middle. 

Current suggested workaround for all DB's solution is to use EF6 under a
separate .net project. 

Any suggestion?  

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: 04 February 2016 17:00
To: FarjadFarid(ChkNet); pgsql-general@postgresql.org
Subject: Re: [GENERAL] Asp.net 5 and EF6

On 02/04/2016 08:46 AM, FarjadFarid(ChkNet) wrote:
> Hi I am trying to use certain features of Asp.net 5 using postgresql 
> using EF6.
>
> I have downloaded the latest unstable version of npgsql and tried to 
> connect to my db.
>
> But no luck.
I am not a nppsql user, so some generic question.

Can you explain in more detail what the errors are?

>
> I have posted a request to stackoverflow.com but haven't had any 
> response at all.
>
> It is easy to connect to Sql Server using EF6 under asp.net 5.
>
> So I would have thought as an interim measure until EF7 is ready which 
> will be quite some time to come,
>
> it should be possible to connect to postgresql using EF6.

Have you looked here?:

http://www.npgsql.org/doc/ef6.html

>
> Any ideas how?
>
> Many thanks.
>


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



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

Re: [GENERAL] Asp.net 5 and EF6

[Adrian Klaver]

On 02/04/2016 08:46 AM, FarjadFarid(ChkNet) wrote:

Hi I am trying to use certain features of Asp.net 5 using postgresql
using EF6.

I have downloaded the latest unstable version of npgsql and tried to
connect to my db.

But no luck.

I am not a nppsql user, so some generic question.

Can you explain in more detail what the errors are?



I have posted a request to stackoverflow.com but haven’t had any
response at all.

It is easy to connect to Sql Server using EF6 under asp.net 5.

So I would have thought as an interim measure until EF7 is ready which
will be quite some time to come,

it should be possible to connect to postgresql using EF6.


Have you looked here?:

http://www.npgsql.org/doc/ef6.html



Any ideas how?

Many thanks.




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


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

[GENERAL] Asp.net 5 and EF6

[FarjadFarid(ChkNet)]

 

Hi I am trying to use certain features of Asp.net 5 using postgresql using
EF6. 

 

I have downloaded the latest unstable version of npgsql and tried to connect
to my db. 

But no luck. 

 

I have posted a request to stackoverflow.com but haven't had any response at
all. 

 

It is easy to connect to Sql Server using EF6 under asp.net 5. 

 

So I would have thought as an interim measure until EF7 is ready which will
be quite some time to come, 

it should be possible to connect to postgresql using EF6. 

 

Any ideas how? 

 

Many thanks. 

Re: [GENERAL] [SQL] Q: documentation bug ?

[Tom Lane]

Karsten Hilbert  writes:
> On Thu, Feb 04, 2016 at 12:00:45PM +0100, Vik Fearing wrote:
>>> REINDEX [ ( { VERBOSE } [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | 
>>> SYSTEM } name

>>> Does this constitute a bug in the documentation or in the Debian version of 
>>> PG ?

>> Neither.  It's a little bit obscure because {} and [] mean special
>> things, but () doesn't.

> Eagle-eyed !

Seems to me the syntax BNF here is bringing the problem on itself by using
{ ... } when in fact only one alternative is available.  I don't find
{ FOO | BAR } especially hard to read, but { FOO } is confusing because
you expect the {}'s to mean something and they really do not.

I'd be inclined to reduce this to

REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name

We can put back the extra decoration when and if a second option arrives.

regards, tom lane


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

Re: [GENERAL] Hot standby and xlog on a ramdisk

[David Steele]

On 2/4/16 9:46 AM, Tore Halvorsen wrote:
> On Thu, Feb 4, 2016 at 3:30 PM, Scott R Mead  > wrote:
> 
>> > Can a hot standby run correctly with the xlog-files on a
>> ramdisk?
>> >
>>
>> Yes, but, if you lose any, you'll need to rebuild the standby.
>>
>>
>> Even if the master and archive contain all the wal files?
>>
> If you have the file somewhere, you're okay, whether it's on the
> master, standby or an archive host.  If you're using log-shipping,
> you'll need all the WALs.
> 
> 
> So, just to verify my understanding. Using a ramdisk for pg_xlog on a
> hot standby slave should be ok in all cases as long as the archive and
> master retains all wal files that are not applied?

Yes.  If the pg_xlog ramdisk is lost on the standby then Postgres will
start throwing errors but there will be no corruption of the heap.  It's
possible that Postgres will terminate at this point, but after
remounting the ramdisk you can restart Postgres on the standby and
everything will be fine.

If something goes really horribly wrong you can always rebuild the standby.

-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Hot standby and xlog on a ramdisk

[Tore Halvorsen]

On Thu, Feb 4, 2016 at 3:30 PM, Scott R Mead  wrote:

> > Can a hot standby run correctly with the xlog-files on a ramdisk?
>> >
>>
>> Yes, but, if you lose any, you'll need to rebuild the standby.
>>
>>
> Even if the master and archive contain all the wal files?
>
> If you have the file somewhere, you're okay, whether it's on the master,
> standby or an archive host.  If you're using log-shipping, you'll need all
> the WALs.
>

So, just to verify my understanding. Using a ramdisk for pg_xlog on a hot
standby slave should be ok in all cases as long as the archive and master
retains all wal files that are not applied?

I maintain a fairly active database (>100GB of wal files every day) and
putting the wal files on a ramdisk (only for the slaves of course) helped
quite a lot.


-- 
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
 Tore Halvorsen || +052 0553034554

Re: [GENERAL] Hot standby and xlog on a ramdisk

[Scott R Mead]

On 02/04/2016 09:25 AM, Tore Halvorsen wrote:
>
> On 02/04/2016 09:15 AM, Tore Halvorsen wrote:
> > Can a hot standby run correctly with the xlog-files on a ramdisk?
> >
>
> Yes, but, if you lose any, you'll need to rebuild the standby.
>
>
> Even if the master and archive contain all the wal files?
>
If you have the file somewhere, you're okay, whether it's on the master,
standby or an archive host.  If you're using log-shipping, you'll need
all the WALs.
> -- 
> Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
>  Tore Halvorsen || +052 0553034554

-- 
Scott Mead
OpenSCG 
http://www.openscg.com
PostgreSQL, Java & Linux Experts

Re: [GENERAL] Hot standby and xlog on a ramdisk

[Tore Halvorsen]

>
> On 02/04/2016 09:15 AM, Tore Halvorsen wrote:
> > Can a hot standby run correctly with the xlog-files on a ramdisk?
> >
>
> Yes, but, if you lose any, you'll need to rebuild the standby.
>
>
Even if the master and archive contain all the wal files?


-- 
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
 Tore Halvorsen || +052 0553034554

Re: [GENERAL] Hot standby and xlog on a ramdisk

[Scott R Mead]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256



On 02/04/2016 09:15 AM, Tore Halvorsen wrote:
> Hi,
> 
> Can a hot standby run correctly with the xlog-files on a ramdisk?
> 

Yes, but, if you lose any, you'll need to rebuild the standby.


> -- Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul] 
>  Tore Halvorsen || +052 0553034554

- -- 
Scott Mead
OpenSCG 
http://www.openscg.com
PostgreSQL, Java & Linux Experts
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iQEcBAEBCAAGBQJWs13RAAoJEMpZLrKS7LGMa9IH/3BE05zIriu1x2zw4FwCMlCc
22aiV1G4Qt2rgnVakV10Cad/hZ6nL9xRnZx+wcAGN3kN3SQJfTL71rL/e2kXzr8J
OWIrspLmCjpnRlO0tPkOYtf7nCsWMo2B4iyqbvZSUuK8vyWnp5DXwdqBZcDmhQGq
r3rccwB1ZCsn5WMato182bU90m6pKRegr11RUKjwHKpM6AfW05WDqZwK3m7DRtAZ
g9JE3B/lcdFzTINo5rpQpABy7ONGs92/E9GvrewAFgU22SKy1LZl0KuPHnohgQGb
5H+zoBzdh3fI10+tNKQMmshJg2XyZMw7YFahdugaJVsUs2hFDTfVVdYUNvlVmaQ=
=cryn
-END PGP SIGNATURE-


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

[GENERAL] Hot standby and xlog on a ramdisk

[Tore Halvorsen]

Hi,

Can a hot standby run correctly with the xlog-files on a ramdisk?

-- 
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
 Tore Halvorsen || +052 0553034554

Re: [GENERAL] reverse proxy to postgresql with haproxy

[Scott R Mead]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256


On 02/04/2016 03:56 AM, Aviel Buskila wrote:
> Hey everyone, 
> I am trying to configure reverse proxy to postgresql using haproxy,
for this example I have the following architecture:
> - node1
> - node2
> - haproxysrv
>
> Streaming replication between node1 and node2 and auto failover with
repmgr.
>
> When I issue a connection to haproxysrv to connect, I get a
pg_hba.conf error telling that I haven't configured the haproxysrv to
connect to the database.
>
> The reason that this scenario occurs is that:
> HAProxy establishes 2 separate connections, 1 between the client and
haproxy and one between haproxy and postgresql. the connection made
haproxy haproxy and postgresql contains source ip of the haproxy itself.
>
> HAProxy offers a "transparent" ip using proxy protocol.
>
> According to haproxy docs and forums this feature is feasible only if
the application is proxy protocol aware.
>
> My question are:
> 1. is there any other solution for this issue?
> 2. is there anyone who knows if postgresql is proxy protocol aware?
>

PostgreSQL only cares about who is talking directly to it.  You will
have to put the HAProxy server's IP address in your pg_hba.conf.  This
is normal and to be expected.   I've configured it many times and it
works well.

Aside from the simple issue of pg_hba.conf, I would just be mindful,
HAProxy is just a 'dumb' load balancer, by that, I mean that it isn't
really aware of who the master *should* be.  If you're not careful, you
can end up easily causing a split-brain scenario.  I'm not sure if
repmgr deals with this or not (it may), but, you want to make sure that,
if you failover, you fence that old master to the point that HAProxy
cannot send requests to it any longer.



> Best regards,
> Aviel B.

- -- 
Scott Mead
OpenSCG 
http://www.openscg.com
PostgreSQL, Java & Linux Experts
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iQEcBAEBCAAGBQJWs1uJAAoJEMpZLrKS7LGMxncIAKEttG5ntfrOXOxpwtQQT+Cr
nTBTI3QLwNuWryk01nqDwYNh8G97heEv1ag1sdsdXiSfYuoar0BMHuTAH/yPozfv
xa/NkpnDTaPoKiH/kkGolt51PCKQ77VjsCZZqwg18XMuHWUM25Sxj08lQJB08fYK
v2SI79GlhfUvOoanHIWu95RfhO+cWeK1cvjYnKDXmxn20O76olHIZgxX/NG9bWVL
XaV2Y3KVkemwOaXc766ITSVJQacwHGLmBDYZXkuI4o88ILJGiMTTymkIyngKUkhA
eqtCuJx2Wx6FzUuKTEnM9M0CwC4VWyVtYik2teEBmP37VSPZ4Pk27FOBf13mzEc=
=p84R
-END PGP SIGNATURE-

Re: [GENERAL] workarounds for ci_text

[Bill Moran]

On Thu, 4 Feb 2016 13:48:37 +0200
Heine Ferreira  wrote:
> 
> As far as I can tell, you can't restrict the length of a ci_text field like
> char(5) or varchar(5)?
> Let's say you got a stock table and you want an alphanumeric stock
> code that is the primary key but it must be case insensitive can I do
> something like this:
> 
> create table stock(stock code varchar(5), stock_desc varchar(50))
> primary key pk_stock_code ilike(stock_code)

You may be better served by using a check constraint to enforce
the length limit, or even creating a domain:
http://www.postgresql.org/docs/9.5/static/ddl-constraints.html
http://www.postgresql.org/docs/9.5/static/sql-createdomain.html

But that depends on whether you're only trying to enforce the
uniqueness or if you want things such as case insensative matching
of the key. Your approach will only give you the former, whereas
CITEXT will give you both.

I don't think your syntax will work, though. I'm guessing that
PRIMARY KEY pk_stock_code lower(stock_code) will, though.

-- 
Bill Moran


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

[GENERAL] workarounds for ci_text

[Heine Ferreira]

Hi,

As far as I can tell, you can't restrict the length of a ci_text field like
char(5) or varchar(5)?
Let's say you got a stock table and you want an alphanumeric stock
code that is the primary key but it must be case insensitive can I do
something like this:

create table stock(stock code varchar(5), stock_desc varchar(50))
primary key pk_stock_code ilike(stock_code)

Will this work? Is this the right syntax?

Thanks

H.F.


-- 
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] [SQL] Q: documentation bug ?

[Karsten Hilbert]

On Thu, Feb 04, 2016 at 12:00:45PM +0100, Vik Fearing wrote:

>>  REINDEX [ ( { VERBOSE } [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE 
>> | SYSTEM } name
...
>> Does this constitute a bug in the documentation or in the Debian version of 
>> PG ?
> 
> Neither.  It's a little bit obscure because {} and [] mean special
> things, but () doesn't.

Eagle-eyed !

>  The syntax is therefore:
> 
> REINDEX (VERBOSE) DATABASE gnumed_v21;

Thank you !  :-)

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

[GENERAL] [SQL] Q: documentation bug ?

[Karsten Hilbert]

Hello all,

the online documentation for REINDEX

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

talks about VERBOSE

Synopsis

REINDEX [ ( { VERBOSE } [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE 
| SYSTEM } name

[...]

VERBOSE

Prints a progress report as each index is reindexed.

but

root@hermes:~/bin# psql -d gnumed_v21 -U gm-dbo
Ausgabeformat ist „wrapped“.
Erweiterte Anzeige ist an.
psql (9.5.0)
Geben Sie „help“ für Hilfe ein.

gnumed_v21=> select version();
-[ RECORD 1 
]---
version | PostgreSQL 9.5.0 on i686-pc-linux-gnu, compiled by gcc 
(Debian 5.3.1-5) 5.3.1 20160101, 32-bit

gnumed_v21=> reindex
DATABASE  INDEX SCHEMASYSTEMTABLE

gnumed_v21=> reindex verbose database 'gnumed_v21';
ERROR:  syntax error at or near "verbose"
ZEILE 1: reindex verbose database 'gnumed_v21';
 ^
gnumed_v21=>

Does this constitute a bug in the documentation or in the Debian version of PG ?

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


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


-- 
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] query from two tables & concat the result

[arnaud gaboury]

On Wed, Feb 3, 2016 at 8:19 PM, Raymond O'Donnell  wrote:
> On 03/02/2016 14:05, arnaud gaboury wrote:

 thetradinghall=> SELECT u.username ||'@'||d.domain as email_address
 FROM email.mailusers u
 INNER JOIN
 email.domainlist d
 ON
 (u.domain_id=d.id);

email_address
 ---
  arnaud.gabo...@thetradinghall.com
 (1 row)
 -

 As for the cleaning of ID, I dropped id and changed both primary keys.
 Thank you so much for your prompt answer and help.
>>
>> In fact I kept the id for table domainlist (changed the name
>> accordingly your advise). If I remove the id column, I will not be
>> able anymore to do the above SELECT , no?
>> The condition (u.domain_id=d.id) will no more be possible.
>>
>> Am I wrong?
>
> You're right - you'll need to use the domain name as the foreign key
> instead. So your tables will look like this:
>
> CREATE TABLE domains (
> domain_name text not null primary key,
> 
> );
>
> CREATE TABLE mailusers (
> username text not null,
> password text not null,
> domain_name text not null,
> created timestamp with time zone not null default now(),
> 
> constraint users_pk primary key (username, domain_name),
> constraint users_domains_fk foreign key (domain_name)
>   references domains(domain_name)
> );
>
> And then your query would look something like this:
>
> select u.username ||'@'||d.domain as email_address
> from mailusers u
> inner join domains d on (u.domain_name = d.domain_name)

Very good. I changed tables accordingly. No more the id columns now.
Thank you so much.


> ...
>
>
> HTH,
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie



-- 

google.com/+arnaudgabourygabx


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

[GENERAL] reverse proxy to postgresql with haproxy

[Aviel Buskila]

Hey everyone,
I am trying to configure reverse proxy to postgresql using haproxy, for
this example I have the following architecture:
- node1
- node2
- haproxysrv

Streaming replication between node1 and node2 and auto failover with repmgr.

When I issue a connection to haproxysrv to connect, I get a pg_hba.conf
error telling that I haven't configured the haproxysrv to connect to the
database.

The reason that this scenario occurs is that:
HAProxy establishes 2 separate connections, 1 between the client and
haproxy and one between haproxy and postgresql. the connection made haproxy
haproxy and postgresql contains source ip of the haproxy itself.

HAProxy offers a "transparent" ip using proxy protocol.

According to haproxy docs and forums this feature is feasible only if the
application is proxy protocol aware.

My question are:
1. is there any other solution for this issue?
2. is there anyone who knows if postgresql is proxy protocol aware?

Best regards,
Aviel B.