date:20180929

Re: ORM

2018-09-29 Thread marcelo





On 29/09/2018 16:09 , Edson Carlos Ericksson Richter wrote:

Em 28/09/2018 19:49, marcelo escreveu:
For a new big and convoluted project I (am/was) using Devart´s 
LinqConnect as ORM.
But today I experienced some inexplicable "object reference not set 
to an instance of an object" exceptions or other more specific to 
this libraries.

I would wish to change the ORM.
Some experiences would be appreciated. Of course, I prefer open 
source software, at least to chase errors thru debugging.

TIA

---
El software de antivirus Avast ha analizado este correo electrónico 
en busca de virus.

https://www.avast.com/antivirus





For .Net I know nothing.
For Java, at other side, EclipseLink is fantastic.

Regards,

Edson.



Thank you. Do you know about a porting to .NET?


---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: ORM

2018-09-29 Thread Dmitry Igrishin

сб, 29 сент. 2018 г. в 1:50, marcelo :
>
> For a new big and convoluted project I (am/was) using Devart´s
> LinqConnect as ORM.
> But today I experienced some inexplicable "object reference not set to
> an instance of an object" exceptions or other more specific to this
> libraries.
> I would wish to change the ORM.
The best ORM is vanila SQL.

Re: ORM

2018-09-29 Thread Edson Carlos Ericksson Richter


Em 28/09/2018 19:49, marcelo escreveu:
For a new big and convoluted project I (am/was) using Devart´s 
LinqConnect as ORM.
But today I experienced some inexplicable "object reference not set to 
an instance of an object" exceptions or other more specific to this 
libraries.

I would wish to change the ORM.
Some experiences would be appreciated. Of course, I prefer open source 
software, at least to chase errors thru debugging.

TIA

---
El software de antivirus Avast ha analizado este correo electrónico en 
busca de virus.

https://www.avast.com/antivirus





For .Net I know nothing.
For Java, at other side, EclipseLink is fantastic.

Regards,

Edson.

Re: ORM

2018-09-29 Thread Adrian Klaver


On 9/29/18 6:47 AM, marcelo wrote:



On 28/09/2018 21:39 , Adrian Klaver wrote:

On 9/28/18 3:49 PM, marcelo wrote:
For a new big and convoluted project I (am/was) using Devart´s 
LinqConnect as ORM.
But today I experienced some inexplicable "object reference not set 
to an instance of an object" exceptions or other more specific to 
this libraries.

I would wish to change the ORM.
Some experiences would be appreciated. Of course, I prefer open 
source software, at least to chase errors thru debugging.


Information that might help folks steer you to alternatives:

1) What programming language(s) are you using?

/C#, from 4.5 framework./



Have you looked at:

http://www.npgsql.org/



2) The OS'es involved

/Linux for the database server, Windows for client machines.//
/

//
3) Are you using any frameworks between the database and the end user?
/Yes, a very big one, developed by me (or using open source libraries at 
some spots), which tries to implement MVP pattern. It´s near completely 
agnostic regarding the DAL layer, but I cannot deny that it may be 
somewhat influenced by the Devart´s model.//

/

//
4) Is an ORM even necessary?
/Yes; there are around 50 entities and with lots of foreign keys. It´s 
well worth to have pre-plumbed the code to get "fathers" and "child 
collections". Ah, and I´m working alone./




TIA

---
El software de antivirus Avast ha analizado este correo electrónico 
en busca de virus.

https://www.avast.com/antivirus









 
	Libre de virus. www.avast.com 
 



<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>



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

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-09-29 Thread Adrian Klaver


On 9/28/18 11:35 PM, Carl Sverre wrote:

*Context*
I am using row-level security along with triggers to implement a pure 
SQL RBAC implementation. While doing so I encountered a weird behavior 
between INSERT triggers and SELECT row-level security policies.


*Question*
I have posted a very detailed question on StackOverflow here:
https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s

For anyone who is just looking for a summary/repro, I am seeing the 
following behavior:


CREATE TABLE a (id TEXT);
ALTER TABLE a ENABLE ROW LEVEL SECURITY;
ALTER TABLE a FORCE ROW LEVEL SECURITY;

CREATE TABLE b (id TEXT);

CREATE POLICY ON a FOR SELECT
USING (EXISTS(
     select * from b where a.id  = b.id 
));

CREATE POLICY ON a FOR INSERT
WITH CHECK (true);

CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
BEGIN
     RAISE NOTICE USING MESSAGE = 'inside trigger handler';
     INSERT INTO b (id) VALUES (NEW.id);
     RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reproTrigger BEFORE INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE reproHandler();

INSERT INTO a VALUES ('fails') returning id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

Rather than the error, I expect that something along these lines should 
occur instead:


1. A new row ('fails') is staged for INSERT
2. The BEFORE trigger fires with NEW set to the new row
3. The row ('fails') is inserted into b and returned from the trigger 
procedure unchanged

4. The INSERT's WITH CHECK policy true is evaluated to true
5. The SELECT's USING policy select * from b where a.id  = 
b.id  is evaluated.  *This should return true due to step 3*

6. Having passed all policies, the row ('fails') is inserted in table
7. The id (fails) of the inserted row is returned

If anyone can point me in the right direction I would be extremely thankful.


When I tried to reproduce the above I got:

test=# CREATE POLICY ON a FOR SELECT
test-# USING (EXISTS(
test(# select * from b where a.id = b.id
test(# ));
ERROR:  syntax error at or near "ON"
LINE 1: CREATE POLICY ON a FOR SELECT
  ^
test=#
test=# CREATE POLICY ON a FOR INSERT
test-# WITH CHECK (true);
ERROR:  syntax error at or near "ON"
LINE 1: CREATE POLICY ON a FOR INSERT

Changing your code to:

CREATE TABLE a (id TEXT);
ALTER TABLE a ENABLE ROW LEVEL SECURITY;
ALTER TABLE a FORCE ROW LEVEL SECURITY;

CREATE TABLE b (id TEXT);

CREATE POLICY a_select ON a FOR SELECT
USING (EXISTS(
select * from b where a.id = b.id
));

CREATE POLICY a_insert ON a FOR INSERT
WITH CHECK (true);

CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE USING MESSAGE = 'inside trigger handler';
INSERT INTO b (id) VALUES (NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reproTrigger BEFORE INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE reproHandler();

Resulted in:

test=# INSERT INTO a VALUES ('fails') returning id;
NOTICE:  inside trigger handler
  id
---
 fails
(1 row)

INSERT 0 1
test=# select * from a;
  id
---
 fails
(1 row)




Carl Sverre

http://www.carlsverre.com



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

Re: Why my query not using index to sort?

2018-09-29 Thread Arup Rakshit

When I keep the sort off, I see it is executing much more faster.

set enable_sort = off;  
explain analyze select
 *
from
"standard_workitems"
where
"standard_workitems"."deleted_at" is null
and "standard_workitems"."company_id" = 
'6fed40b7-fdd7-4efb-a163-c2b42e6486ae'
order by
item_code asc;

Index Scan using standard_workitems_partial_index_idx_1_1 on standard_workitems 
 (cost=0.42..5802.04 rows=1697 width=763) (actual time=0.018..1.076 rows=2071 
loops=1)
  Index Cond: (company_id = '6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid)
Planning time: 0.191 ms
Execution time: 1.210 ms


I have no idea why PG thinks in memory sort will be cheaper.. 


Thanks,

Arup Rakshit
a...@zeit.io



> On 29-Sep-2018, at 9:40 PM, Arup Rakshit  wrote:
> 
> Hello Tom,
> 
> I have another query, where I am expecting the sort from index, but it is in 
> memory and takes lot of time.
> 
> Query:
> 
> explain analyze select
>*
> from
>   "standard_workitems"
> where
>   "standard_workitems"."deleted_at" is null
>   and "standard_workitems"."company_id" = 
> '6fed40b7-fdd7-4efb-a163-c2b42e6486ae'
> order by
>   standard_workitems.item_code asc;
> 
> Explain plan:
> 
> Sort  (cost=3454.03..3458.18 rows=1660 width=810) (actual time=20.302..20.502 
> rows=2071 loops=1)
>   Sort Key: item_code
>   Sort Method: quicksort  Memory: 800kB
>   ->  Bitmap Heap Scan on standard_workitems  (cost=57.29..3365.25 rows=1660 
> width=810) (actual time=0.297..0.781 rows=2071 loops=1)
> Recheck Cond: ((company_id = 
> '6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid) AND (deleted_at IS NULL))
> Heap Blocks: exact=139
> ->  Bitmap Index Scan on standard_workitems_partial_index_idx_1_1  
> (cost=0.00..56.87 rows=1660 width=0) (actual time=0.272..0.272 rows=2071 
> loops=1)
>   Index Cond: (company_id = 
> '6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid)
> Planning time: 0.199 ms
> Execution time: 20.688 ms
> 
> Indexes I have:
> 
> Indexes:
> "standard_workitems_pkey" PRIMARY KEY, btree (id)
> "index_standard_workitems_on_company_id" btree (company_id)
> "index_standard_workitems_on_deleted_at" btree (deleted_at)
> "index_standard_workitems_on_item_code" btree (item_code)
> "index_standard_workitems_on_workitem_category_id" btree 
> (workitem_category_id)
> "standard_workitems_partial_index_idx_1_1" btree (company_id, item_code) 
> WHERE deleted_at IS NULL
> 
> 
> 
> Thanks,
> 
> Arup Rakshit
> a...@zeit.io 
> 
> 
> 
>> On 28-Sep-2018, at 7:07 PM, Tom Lane > > wrote:
>> 
>> Arup Rakshit mailto:a...@zeit.io>> writes:
>>> My query is not using name index to sort the result.
>> 
>> Given the rowcounts here, I think the planner is making the right choice.
>> Sorting 70-some rows with a Sort node is probably cheaper than doing
>> random disk I/O to get them in sorted order.  With more rows involved,
>> it might make the other choice.
>> 
>> As a testing measure (don't do it in production!), you could set
>> enable_sort = off, which will force the planner to pick a non-Sort
>> plan if possible.  Then you could see whether that's actually faster
>> or slower, and by how much.
>> 
>>  regards, tom lane
>

Re: Why my query not using index to sort?

2018-09-29 Thread Arup Rakshit

Hello Tom,

I have another query, where I am expecting the sort from index, but it is in 
memory and takes lot of time.

Query:

explain analyze select
 *
from
"standard_workitems"
where
"standard_workitems"."deleted_at" is null
and "standard_workitems"."company_id" = 
'6fed40b7-fdd7-4efb-a163-c2b42e6486ae'
order by
standard_workitems.item_code asc;

Explain plan:

Sort  (cost=3454.03..3458.18 rows=1660 width=810) (actual time=20.302..20.502 
rows=2071 loops=1)
  Sort Key: item_code
  Sort Method: quicksort  Memory: 800kB
  ->  Bitmap Heap Scan on standard_workitems  (cost=57.29..3365.25 rows=1660 
width=810) (actual time=0.297..0.781 rows=2071 loops=1)
Recheck Cond: ((company_id = 
'6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid) AND (deleted_at IS NULL))
Heap Blocks: exact=139
->  Bitmap Index Scan on standard_workitems_partial_index_idx_1_1  
(cost=0.00..56.87 rows=1660 width=0) (actual time=0.272..0.272 rows=2071 
loops=1)
  Index Cond: (company_id = 
'6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid)
Planning time: 0.199 ms
Execution time: 20.688 ms

Indexes I have:

Indexes:
"standard_workitems_pkey" PRIMARY KEY, btree (id)
"index_standard_workitems_on_company_id" btree (company_id)
"index_standard_workitems_on_deleted_at" btree (deleted_at)
"index_standard_workitems_on_item_code" btree (item_code)
"index_standard_workitems_on_workitem_category_id" btree 
(workitem_category_id)
"standard_workitems_partial_index_idx_1_1" btree (company_id, item_code) 
WHERE deleted_at IS NULL



Thanks,

Arup Rakshit
a...@zeit.io



> On 28-Sep-2018, at 7:07 PM, Tom Lane  wrote:
> 
> Arup Rakshit  writes:
>> My query is not using name index to sort the result.
> 
> Given the rowcounts here, I think the planner is making the right choice.
> Sorting 70-some rows with a Sort node is probably cheaper than doing
> random disk I/O to get them in sorted order.  With more rows involved,
> it might make the other choice.
> 
> As a testing measure (don't do it in production!), you could set
> enable_sort = off, which will force the planner to pick a non-Sort
> plan if possible.  Then you could see whether that's actually faster
> or slower, and by how much.
> 
>   regards, tom lane

Re: How to maintain the csv log files in pg_log directory only for past 30 days

2018-09-29 Thread Adrian Klaver


On 9/28/18 7:05 PM, Raghavendra Rao J S V wrote:

Hi All,

Hope you all are recommending below settings to maintain only max 30 
days logs in *pg_log* directory. Please correct me if I am wrong.


Well it would actually be 31 days as:

http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html

%d
Replaced by the day of the month as a decimal number [01,31]. [ 
tm_mday]


Not sure if that matters or not.



log_filename = 'postgresql-%d.log'
log_truncate_on_rotation = 'on',

Regards,
Raghavendra Rao

On Sat, 29 Sep 2018 at 04:24, Michael Paquier > wrote:


On Fri, Sep 28, 2018 at 06:19:16AM -0700, Adrian Klaver wrote:
 > If log_truncate_on_rotation = 'on', correct?

Yup, thanks for precising.
--
Michael



--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425



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

Re: ORM

2018-09-29 Thread marcelo




On 28/09/2018 21:39 , Adrian Klaver wrote:

On 9/28/18 3:49 PM, marcelo wrote:
For a new big and convoluted project I (am/was) using Devart´s 
LinqConnect as ORM.
But today I experienced some inexplicable "object reference not set 
to an instance of an object" exceptions or other more specific to 
this libraries.

I would wish to change the ORM.
Some experiences would be appreciated. Of course, I prefer open 
source software, at least to chase errors thru debugging.


Information that might help folks steer you to alternatives:

1) What programming language(s) are you using?

/C#, from 4.5 framework./


2) The OS'es involved

/Linux for the database server, Windows for client machines.//
/

//
3) Are you using any frameworks between the database and the end user?
/Yes, a very big one, developed by me (or using open source libraries at 
some spots), which tries to implement MVP pattern. It´s near completely 
agnostic regarding the DAL layer, but I cannot deny that it may be 
somewhat influenced by the Devart´s model.//

/

//
4) Is an ORM even necessary?
/Yes; there are around 50 entities and with lots of foreign keys. It´s 
well worth to have pre-plumbed the code to get "fathers" and "child 
collections". Ah, and I´m working alone./




TIA

---
El software de antivirus Avast ha analizado este correo electrónico 
en busca de virus.

https://www.avast.com/antivirus










---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

[proposal] pg_stat_statements: extension timing instrumentation

2018-09-29 Thread legrand legrand



Don't you have been surprised by the duration of a query (seen in psql with
\timing)
and the duration for the same query found in pgss ?

It seems that writting the first query text to file takes some time,
that high execution rate on the same query could generate waits on locks
as when pgss is under pressure with too much distinct queries to store
compared 
to the max statements allowed.

All those "waits" are co-located in pgss_store function. What about adding a
pgss_time counter 
in pgss to measure the duration of pgss_store (a guc could be added to
enable/disable this) ?

Knowing that there are also plans to add a planing counter in PG12, I would
suggest to add

plan_time,
exec_time,
pgss_time

the sum of those 3 counters being total_time.

This could help in investigating write contentions in pg_stat_statements
query file,
helping to define that max queries should be increased, ...

A prototype is available on demand.

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Out of Memory

2018-09-29 Thread Peter J. Holzer

On 2018-09-28 07:23:59 +0200, Laurenz Albe wrote:
> Rob Sargent wrote:
> > > Christoph Moench-Tegeder wrote:
> > > > ## Laurenz Albe (laurenz.a...@cybertec.at):
> > > > 
> > > > > vm.overcommit_memory = 2
> > > > > vm_overcommit_ratio = 100
> > > > > 
> > > > > Linux commits (swap * overcommit_ratio * RAM / 100),
> > > > 
> > > >  ^
> > > >  That should be a "+".
> > > 
> > > Yes; shame on me for careless typing, and thank you for the
> > > correction.
> > 
> > Are there any parentheses needed in that formula?
> 
> No.  It is swap space plus a certain percentage of RAM.
> 
> I don't know how the Linux developers came up with that
> weird formula.

I suspect they copied it from some other Unix.

Traditionally, Unix allocated all memory in the swap space. You could
say that the swap space was the "real memory" and RAM was a cache for
that (Hence the rule that swap must be at least as large as RAM and
should preferrably be 2 or 4 times the size of RAM). So, when Unixes
started to allow allocating more virtual memory than swap space, they
were "overcommitting". 

But for Linux that doesn't make much sense, since a page lived either in
RAM /or/ in swap right from the start, so the limit was always RAM+swap,
not swap alone, and you are only overcommitting if you exceeded the size
of the sum. The overcommitment in Linux is of a different kind: Linux
uses copy on write whereever it can (e.g. when forking processes, but
also when mallocing memory), and a CoW page may or may not be written in
the future. It only needs additional space when it's actually written,
so by counting the page only once (hoping that there will be enough
space if and when that page is written) the kernel is overcommitting
memory. 

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 

signature.asc
Description: PGP signature

Re: Why my query not using index to sort?

2018-09-29 Thread Arup Rakshit

Hello Adrian,


I used to consider this time as the sql execution time approximately. I’ll do 
some research and get back to you. Thanks for mentioning it.


Thanks,

Arup Rakshit
a...@zeit.io



> On 29-Sep-2018, at 2:50 AM, Adrian Klaver  wrote:
> 
> On 9/28/18 12:14 PM, Arup Rakshit wrote:
>> Forgot to mention in my previous email, it was a quick send click. Sorry for 
>> that.
>> In the gist you need to see all the line with Vessel Load(.. . I load the 
>> page multiple times to catch the different times, so you will the line 
>> multiple times there in the log file.
> 
> Do you know what Vessel Load () is actually measuring?
> 
> To me it looks like it is covering both the query(including ROR overhead) and 
> the HTTP request/response cycle.
> 
> Also have you looked at:
> 
> https://guides.rubyonrails.org/debugging_rails_applications.html#impact-of-logs-on-performance
> 
>> Thanks,
>> Arup Rakshit
>> a...@zeit.io 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: ORM

Re: ORM

Re: ORM

Re: ORM

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

Re: Why my query not using index to sort?

Re: Why my query not using index to sort?

Re: How to maintain the csv log files in pg_log directory only for past 30 days

Re: ORM

[proposal] pg_stat_statements: extension timing instrumentation

Re: Out of Memory

Re: Why my query not using index to sort?

12 matches

Site Navigation

Mail list logo

Footer information