Re: Connection terminated but client didn't realise

2019-12-02 Thread David Wheeler 
>  Is the application remote from the database server?  My gut reaction to this 
> type of report is "something timed out the network connection", but there 
> would have to be a router or firewall or the like in between to make that a 
> tenable explanation.
>  If that is the issue, you should be able to fix it by making the server's 
> TCP keepalive settings more aggressive.

Yes the application server is separate from the database server, and the 
application is running within docker which I suspect adds some complexity too. 
I had suspicions about something in the middle closing the connection too, but 
your email has clarified my thinking a bit. 

TCP Keepalive appears to be enabled on the application server and within 
docker, and the client holds the allegedly dead connection for much longer 
(24h) than the keepalive should take to kill it (<3h), so I think the next step 
is to try to identify the connection at the OS level with netstat to see what 
state it's in. 

Thanks for your help. 


Regards, 
 
David

On 2/12/19, 11:17 pm, "Tom Lane"  wrote:

David Wheeler  writes:
> We have a query that our system runs nightly to refresh materialised 
views. This takes some time to execute (~25 minutes) and then it will usually 
return the results to the application and everything is golden. However 
occasionally we see something like the below, where the query finishes, but the 
connection gets unexpectedly closed from Postgres’ perspective. From the 
application’s perspective the connection is still alive, and it sits there 
forever waiting for the result.

Is the application remote from the database server?  My gut reaction
to this type of report is "something timed out the network connection",
but there would have to be a router or firewall or the like in between
to make that a tenable explanation.

If that is the issue, you should be able to fix it by making the
server's TCP keepalive settings more aggressive.

regards, tom lane

Permission inconsistency with views that call functions

2021-02-23 Thread David Wheeler 
Hi all

I’m seeing some inconsistency with how permissions are enforced within views. 
In particular, if the view accesses a table directly, then the table is 
accessible, however if the view uses a function to access the table then 
permission is denied.

Here’s a demonstration (from pg13.0)

createdb temp

psql temp <<\EOF
create schema hidden;

create table hidden.tab (col1 text);
insert into hidden.tab values ('abc');

create function test() returns text[] as $$
  select array_agg(col1) from hidden.tab;
$$ language sql stable;

create view tv1 as select test();
create view tv2 as select array_agg(col1) from hidden.tab;

grant select on tv1 to public;
grant select on tv2 to public;


create user test password 'test' login;
EOF



PGPASSWORD=test psql -U test temp <<\EOF
\echo select * from tv1;
select * from tv1;
\echo ---
\echo select * from tv2;
select * from tv2;
set jit_inline_above_cost to -1;
\echo ---
\echo select * from tv1; -- no jit inlining
select * from tv1;
EOF

OUTPUT

ERROR:  permission denied for schema hidden
LINE 2:   select array_agg(col1) from hidden.tab;
  ^
QUERY:
  select array_agg(col1) from hidden.tab;

CONTEXT:  SQL function "test" during inlining
---
select * from tv2;
array_agg
---
{abc}
(1 row)

SET
---
select * from tv1; -- no jit inlining
ERROR:  permission denied for schema hidden
LINE 2:   select array_agg(col1) from hidden.tab;
  ^
QUERY:
  select array_agg(col1) from hidden.tab;

CONTEXT:  SQL function "test" during inlining


Is this expected/desirable? Any ideas how I can work around it, short of 
inlining every function manually (if that’s even possible) or granting access 
to the “hidden” schema?

Thanks in advance!

Best regards,

David Wheeler, Inomial Architect
E. dwhee...@dgitsystems.com<mailto:dwhee...@dgitsystems.com>

313 La Trobe Street, Victoria 3000, Australia
+61 3 8820 5200

MELBOURNE . DENPASAR . AUCKLAND
WWW.DGITSYSTEMS.COM<http://www.dgitsystems.com/>


[signature_414257395]<https://www.dgitsystems.com/>

Re: Permission inconsistency with views that call functions

2021-02-23 Thread David Wheeler 
Thanks for the reply

> VIEWs operate as if they were "SECURITY DEFINER".

My concern is that the view is not acting as a security barrier underneath 
which all access is evaluated using the view owner context; in some 
circumstances (when the view calls a function) the access is evaluated using 
the caller’s context.

Caller
   |
   |
  V
- View --   Security definition layer
   |
  | (b)
   ||
(a)|v
   |  Function (not SECURITY DEFINER or explicitly SECURITY INVOKER)
   ||
   ||
   ||
  Vx
   Table


I would expect that everything underneath the View would use the view owner to 
evaluate permissions. However it seems that in scenario (b) it enters a new 
security context from the caller, rather than inheriting it from the view.

> The solution to your issue is possibly to make the function "SECURITY 
> DEFINER".

This works, but I see this as a workaround, because the function is simply a 
utility that makes understanding the data in the table a little easier. Why 
should it be security definer? If you don’t have access to the table you 
shouldn’t be able to use the function to access it.


Regards,

David

On 24/2/21, 2:41 am, "Joe Conway"  wrote:
On 2/22/21 10:32 PM, David Wheeler wrote:
> I’m seeing some inconsistency with how permissions are enforced within views. 
> In
> particular, if the view accesses a table directly, then the table is 
> accessible,
> however if the view uses a function to access the table then permission is 
> denied.

Without looking too closely at the details, I can almost guarantee that the
issue is that FUNCTIONs default to "SECURITY INVOKER" whereas VIEWs operate as
if they were "SECURITY DEFINER". See slide 33 here:

http://joeconway.com/presentations/security-pgcon2020.pdf

The solution to your issue is possibly to make the function "SECURITY DEFINER".

I have mused previously (not sure if I ever did on the lists, but in any case)
that it would be cool if VIEWs could have the option to be either DEFINER or
INVOKER so that VIEWs and FUNCTIONs could be treated the same, but no efforts
have been made in that direction as far as I am aware.

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: How to avoid Trigger ping/pong / infinite loop

2023-02-16 Thread David Wheeler 

>> Are there techniques for situations like this?

Just have two triggers, one for each column, and ensure that if your trigger 
doesn’t change the value then it doesn’t do an update on the other column. Each 
time you do update both triggers will run but only one will make a change, so 
that will break the cycle

For insert case ensure the triggers do nothing if their source col is null

IS as a binary operator

2023-02-25 Thread david wheeler 
Heya folks, 

Recently I've been using a Kotlin library SQLDelight 
<https://cashapp.github.io/sqldelight/2.0.0-alpha05/> which is great but has 
some issues with PostgreSQL support. In particular, for null parameters it 
converts `=` to `IS` - and it treats `IS` as a binary operator (rather than `IS 
NULL` unary operator), which Postgres doesn't like wrt prepared statements

prepare test(text) as (SELECT * FROM test WHERE nullable_col IS $1);
ERROR:  syntax error at or near "$1"
LINE 1: ...epare test(text) as (SELECT * FROM test WHERE nullable_col IS $1);
full details at [1]

I’ve been working on a PR to use `IS NOT DISTINCT FROM` as the `=` substitution 
for nullable parameters rather than `IS`, however unfortunately that’s not 
universally supported either (MySQL). 

Just wondering if anyone has other ideas? I guess there’s no interest in adding 
support for `IS` as basically an alias for `IS NOT DISTINCT FROM`? (and `IS 
NOT` for `IS DISTINCT FROM`)

TIA

David Wheeler

[1] https://github.com/cashapp/sqldelight/issues/3863

Materialized view breaks pg_restore

2019-03-21 Thread David Wheeler 
Hi,

We’re regularly having an issue when restoring dumps of our databases like this


 [exec] CREATE DATABASE "testRestore";
 [exec] pg_restore: [archiver (db)] Error while PROCESSING TOC:
 [exec] pg_restore: [archiver (db)] Error from TOC entry 15728; 0 43798 
MATERIALIZED VIEW DATA fact_tax dbowner@smile-DEV_2019-03-22T09-32-13.338
 [exec] pg_restore: [archiver (db)] could not execute query: ERROR:  
relation "basic" does not exist
 [exec] LINE 1: SELECT chargegst from basic where uid = _account
 [exec]   ^
 [exec] QUERY:  SELECT chargegst from basic where uid = _account
 [exec] CONTEXT:  PL/pgSQL function ar.categorise_gst(integer,integer,date) 
line 7 at IF
 [exec] Command was: REFRESH MATERIALIZED VIEW cube02.fact_tax;


The issue is that there’s a mat view that refers to a plpgsql function that 
refers to a table in the public schema, but it’s not qualified. When we create 
the materialized view, and when we refresh it, the table is in the search path. 
But when restoring from a dump, it’s not.

Is this the desired behaviour? This is an issue for us because 
pg_dump/pg_restore is part of our disaster recovery process, so if we find this 
problem during restore it will mean more downtime.

PG version 9.5.14. I’m attempting to find out now if it’s an issue in more 
recent versions also.


TIA

David Wheeler
Software developer


[cid:2C4D0888-9F8B-463F-BD54-2B60A322210C]


E dwhee...@dgitsystems.com<mailto:dwhee...@dgitsystems.com>
D +61 3 9663 3554  W http://dgitsystems.com
Level 4, 313 La Trobe St, Melbourne VIC 3000.

Re: what happens if a failed transaction is not rolled back?

2023-04-24 Thread David Wheeler 


> On 25 Apr 2023, at 1:47 am, David G. Johnston  
> wrote:
> 
> There isn't anything special about a failed transaction compared to any other 
> transaction that you leave open.

Now I’m curious. Does it have the same impact on performance that an idle in 
transaction connection has? Eg does it prevent vacuum? Does it still hold locks?

David

Re: Return rows in input array's order?

2023-05-09 Thread David Wheeler 


> Hi. With an integer identity primary key table,
> we fetch a number of rows with WHERE id = ANY($1),
> with $1 an int[] array. The API using that query must return
> rows in the input int[] array order, and uses a client-side
> mapping to achieve that currently.
> 
> Is it possible to maintain $1's order directly in SQL? Efficiently?

We’ve done this before with an “order by array_index(id, input_array)”. I 
forget the actual function consider that pseudo code

It was only used for small arrays but never noticed any performance issues

Re: Return rows in input array's order?

2023-05-09 Thread David Wheeler 


>> It was only used for small arrays but never noticed any performance issues
> 
> Hmmm, sounds like this would be quadratic though...

True, but it’s cpu time not io, which tends to be orders of magnitude slower

> I wonder whether the int[] can be turned into a pseudo table with a ROWNUM 
> extra generated column that
> would then be (LEFT) JOIN'd to the accessed table, so that the original array 
> index is readily accessible.
> Would something like this be possible in Postgres' SQL?

The unnest function mentioned above has a “with ordinality” option which gives 
easy access to the array index so a simple join would do the trick

Deadlock with 2 processes waiting on transactionids and locking unrelated tables

2018-02-18 Thread David Wheeler 
---+---
 relation  ||   | 56/2306861 | 41715 | 
RowExclusiveLock | planscheduleitem_plan_company_idx|  |
 relation  ||   | 56/2306861 | 41715 | 
RowExclusiveLock | psi_uid_startdate|  |
 relation  ||   | 56/2306861 | 41715 | 
RowExclusiveLock | psi_planschedule_startdate_starttime |  |
 relation  ||   | 56/2306861 | 41715 | 
RowExclusiveLock | planscheduleitem_pkey|  |
 relation  ||   | 56/2306861 | 41715 | 
RowExclusiveLock | planscheduleitem |  |
 virtualxid| 56/2306861 |   | 56/2306861 | 41715 | 
ExclusiveLock|  |  |
 transactionid ||4089783283 | 56/2306861 | 41715 | 
ExclusiveLock|  |  |
(7 rows)

TIA

Cheers, 

-- David


 <http://www.inomial.com/>

 <http://www.inomial.com/>David Wheeler • software engineer
Inomial Pty Ltd • Automatic Billing <http://www.inomial.com/>
p +61 3 9663 3554


 <http://www.linkedin.com/company/inomial-pty-ltd> 
<https://www.facebook.com/Inomial> <https://twitter.com/inomial>
 <https://twitter.com/inomial>

Re: Deadlock with 2 processes waiting on transactionids and locking unrelated tables

2018-02-19 Thread David Wheeler 
Thanks for your response

> Does any of the two tables have triggers?

Yes the ticket table has a trigger that inserts changes into a ticketstatuslog 
table when the ticket.status column changes and on insert.

ticket_status_insert_trigger AFTER INSERT ON ticket FOR EACH ROW EXECUTE 
PROCEDURE ticket_status_trigger_function()
ticket_status_update_trigger AFTER UPDATE OF ticketstatus ON ticket FOR 
EACH ROW WHEN (old.ticketstatus <> new.ticketstatus) EXECUTE PROCEDURE 
ticket_status_trigger_function()

> What's the database / transaction isolation level?

Both read committed

> Do the updates run in a transaction among other read / write operations 
> within the same transaction ?

Yes they will both have many reads and writes before running the deadlocking 
query. 

Cheers, 

-- David


 <http://www.inomial.com/>

 <http://www.inomial.com/>David Wheeler • software engineer
Inomial Pty Ltd • Automatic Billing <http://www.inomial.com/>
p +61 3 9663 3554


 <http://www.linkedin.com/company/inomial-pty-ltd> 
<https://www.facebook.com/Inomial> <https://twitter.com/inomial>
 <https://twitter.com/inomial>
> On 19 Feb 2018, at 4:43 pm, Rene Romero Benavides  
> wrote:
> 
> Hi. Does any of the two tables have triggers? What's the database / 
> transaction isolation level? Do the updates run in a transaction among other 
> read / write operations within the same transaction ?
> Regards.
> 
> 2018-02-18 23:28 GMT-06:00 David Wheeler  <mailto:da...@inomial.com>>:
> Hi,
> 
> We’re seeing deadlock semi-regularly (0-2 per day) that I’m really having 
> trouble getting to the bottom of. 
> 
> Process 7172 waits for ShareLock on transaction 4078724272 
> ; blocked by process 7186.
> Process 7186 waits for ShareLock on transaction 4078724210 
> ; blocked by process 7172.
> 
> The two queries in question are updates on unrelated tables. Running the 
> queries on their own shows no overlapping entries in pg_locks. 
> 
> Process 7172: update ticket set unread = true where ticketid = $1
> Process 7186: update "planscheduleitem" set "planschedule"=$1 where "psi"=$2
> 
> How can I work out why Postgres has decided that the two processes are in 
> deadlock? Is there an explainer somewhere on transaction level locks? I can’t 
> see anything in the docs besides that they exist. 
> 
> 
> 
> Details below
> 
> select version();
>   version
> ---
>  PostgreSQL 10.2 (Ubuntu 10.2-1.pgdg14.04+1) on x86_64-pc-linux-gnu, compiled 
> by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
> (1 row)
> 
> ---
> 
> 
> after running update "planscheduleitem" set "planschedule"=$1 where "psi"=$2
> 
> SELECT locktype, virtualxid, transactionid, virtualtransaction, pid, mode, 
> relname, page, tuple
> FROM pg_locks LEFT JOIN pg_class ON (relation=oid) where pid =41715;
>locktype| virtualxid | transactionid | virtualtransaction |  pid  |
>mode   | relname | page | tuple
> ---++---++---+--+-+--+---
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_parentticketid   |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_originalticketid |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_tickettypeid_idx |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_subject_idx  |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_closedtime_idx   |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_assignedto_idx   |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_serviceuid_idx   |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_parentuid_idx|  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_createdtime_idx  |  |
>  relation  ||   | 56/2306863

Re: Deadlock with 2 processes waiting on transactionids and locking unrelated tables

2018-02-19 Thread David Wheeler 
Oh I see. Yeah it’s kind of obvious now that you point it out! 

> Btw, do the transactions use explicit locking?


We occasionally use for update so that could be involved. We’ll have a closer 
look at the code involved. 

I’m still curious about why the locks are both transaction locks rather than 
one of them being a row lock. 

Thanks for your help!

Cheers, 

-- David


 <http://www.inomial.com/>

 <http://www.inomial.com/>David Wheeler • software engineer
Inomial Pty Ltd • Automatic Billing <http://www.inomial.com/>
p +61 3 9663 3554


 <http://www.linkedin.com/company/inomial-pty-ltd> 
<https://www.facebook.com/Inomial> <https://twitter.com/inomial>
 <https://twitter.com/inomial>
> On 19 Feb 2018, at 5:54 pm, Rene Romero Benavides  
> wrote:
> 
> My guess is that the transaction doing:
>  
> update "planscheduleitem" set "planschedule"=$1 where "psi"=$2
> 
> updates ticket before reaching that point
> 
> And
> 
> update ticket set unread = true where ticketid = $1
> 
> updates planscheduleitem before that
> 
> Does it make sense to you? Btw, do the transactions use explicit locking?
> 
> 2018-02-18 23:28 GMT-06:00 David Wheeler  <mailto:da...@inomial.com>>:
> Hi,
> 
> We’re seeing deadlock semi-regularly (0-2 per day) that I’m really having 
> trouble getting to the bottom of. 
> 
> Process 7172 waits for ShareLock on transaction 4078724272 
> ; blocked by process 7186.
> Process 7186 waits for ShareLock on transaction 4078724210 
> ; blocked by process 7172.
> 
> The two queries in question are updates on unrelated tables. Running the 
> queries on their own shows no overlapping entries in pg_locks. 
> 
> Process 7172: update ticket set unread = true where ticketid = $1
> Process 7186: update "planscheduleitem" set "planschedule"=$1 where "psi"=$2
> 
> How can I work out why Postgres has decided that the two processes are in 
> deadlock? Is there an explainer somewhere on transaction level locks? I can’t 
> see anything in the docs besides that they exist. 
> 
> 
> 
> Details below
> 
> select version();
>   version
> ---
>  PostgreSQL 10.2 (Ubuntu 10.2-1.pgdg14.04+1) on x86_64-pc-linux-gnu, compiled 
> by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
> (1 row)
> 
> ---
> 
> 
> after running update "planscheduleitem" set "planschedule"=$1 where "psi"=$2
> 
> SELECT locktype, virtualxid, transactionid, virtualtransaction, pid, mode, 
> relname, page, tuple
> FROM pg_locks LEFT JOIN pg_class ON (relation=oid) where pid =41715;
>locktype| virtualxid | transactionid | virtualtransaction |  pid  |
>mode   | relname | page | tuple
> ---++---++---+--+-+--+---
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_parentticketid   |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_originalticketid |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_tickettypeid_idx |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_subject_idx  |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_closedtime_idx   |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_assignedto_idx   |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_serviceuid_idx   |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_parentuid_idx|  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_createdtime_idx  |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_txid |  |
>  relation  ||   | 56/2306863 | 41715 | 
> RowExclusiveLock | ticket_tickettype   |  |
>  relation  ||   | 56/2306863