And you can, you can start it now. Okay. Okay, you turned it on. I think I did. Yeah, the people are starting to to roll in 40m. Okay. So I just wait until it's kind of leveled off, I think. I think we'll start now. We seem to have kind of a leveling off of participants. Of course, people may still join us, but welcome to the first Health Law Seminar of 2021. The Health Law Seminar series is brought to you, brought to us by the Health Law Institute at Schulich School of Law at Dalhousie University. And during our seminar series, we have closed captioning available. If you would like that option while you are viewing the participating in the seminar, there should be a little icon at the bottom right of your screen to invite you to turn on closed captioning. I will let you know that this this seminar will be recorded and publicly available on YouTube. Of course, no one besides the speakers are shown. It doesn't capture the audience in that way. After the talk, we will have a question and answer period. And you can enter your questions through the chat function throughout Dr. Cummings talk. And then you can also use the vote function to if your question is very similar to someone else's question, and you want to bump that question up, you can do that. So without further ado, I would like to introduce Dr. Fiona Kiyunjin, who is an assistant professor in the Department of Family Medicine at McMaster University, and also an adjunct scientist at ICES. Fiona is a family physician, a public health physician and an epidemiologist. She has worked as a family physician in a jail in Ontario since 2007, and she conducts research focused on the health of people who experience imprisonment. Thank you so much, Fiona, for joining us, participating in this. And I turn it over to you. Thanks so much, Martha. And with apologies for doing this, Martha, I don't think you introduced who you are. Do you want to take a minute to introduce yourself? I think you still on mute. I'm Martha Painter. I am a registered nurse and a doctoral candidate in the School of Nursing at Dalhousie University. I'm a research scholar with the Health Law Institute, and I am delighted to be hosting Dr. Kiyunjin today because my research also intersects with health and criminalization. And Dr. Kiyunjin's work is really critical in this field in Canada. She is the leading voice. So thank you so much. Thanks, Martha. I just thought it would be good for people to know who you are also as a great point of contact locally. And so thank you so much. It's a pleasure for me to have the opportunity to speak today. And thanks to Martha for the introduction and to Adelina for inviting me, and also to Marnie for providing the enclosed captioning. I'm just going to share my screen. Excellent. I wanted to start by saying that I have a lot of great memories of or great associations, I should say, with the Law School at Dalhousie because I went to medical school at Dalhousie and spent a lot of time studying in the Law School Library, which at that time was considered the best place to study. So yeah, have lots of good memories and possibly several memories of being very anxious also. But yeah, a pleasure to speak with you today. So I have two objectives for this talk. The first is to describe the health status of people who experience imprisonment in Canada. And the second is to consider opportunities to improve health for this population. And just to let you know as we start, when I use this term imprisonment, I know that in Canada, that is often the term prison is specific to people in federal facilities. But I'm using it in the sense that we use it internationally, where anyone who is in detention or who is incarcerated, whether they're in a provincial or a federal facility, I include those in this broad term imprisonment. So I have four key messages that I'd like to share with you. The first is that imprisonment, again, both including detention and incarceration, is common in Canada. And to start out, I would like to ask you a quiz question. What proportion of adults spend time in jail or prison each year in Ontario? And Adelina, if you're able to put this up, and I'm presenting this for Ontario only because I don't know this information for Nova Scotia. I'm certainly happy for anyone who does know this information to put it into the Q&A. Because I think the chat box is disabled. Yeah. Happy to hear you. Maybe I'll just be quiet for a minute and let you think and put in the answer. So what proportion of adults spend time each year in jail or prison in Ontario? And again, this is likely similar for Nova Scotia. Just give you a few more seconds. Okay. Adelina, I don't know if you can close the poll and we can see the results. Okay, so you can see that about 12% said one in 10,000 42% said one in 3000 32% said one in 1000 9% said one in 300 and 5% said one in 100. So I will close that. So it seems like most people think it's about one in 3000 people who spend time in jails or prisons. It's actually much more common than that. It's one in 300 people. And for those of you who think like this can't be right, which was my initial reaction when I looked at this, I've I've looked at population based data for this population in Ontario. And this is actually only people in the provincial system. So I'm not even including people in the federal system, which shows that there are about 40 to 50,000 people per year who spend at least one night in a jail or prison. And that's of the over the time period that it's between 40 and 50,000 between 11 and 12 million adults in Ontario. So if you take that ratio, you can see that it's between, well, it's always less than one in 300 people. So I think this is important when we talk about the health of this population for a few reasons. One is spending time in prison often reflects poor health. So this is a consequence of having poor health. An obvious example would be having a substance use disorder, which in the context of the criminalization of illicit substance use can often end up leading to incarceration. But also, this is often a significant exposure for poor health outcomes. And we'll come back to that in more detail. But when we talk about the importance of this population, a focus of on the health of this population, I think it really matters to talk about the fact that this is a relatively common experience for people. And in particular, we know that specific subpopulations are overrepresented in people who experience incarceration, such as people who are indigenous, people who are black, and males compared to females. And the last thing is, I think when we think about indicators of our society and what criminal justice system, how it impacts our population, often we only see data presented on the cross sectional population. So on any given day, how many people experience imprisonment and what's the rate of incarceration per 100,000? But of course, it matters not only how many people are incarcerated on any given day, but also over the course of a longer time period, how does how do people interact with our criminal justice system? Okay, so this is a bit more detail. So I've got data here for Ontario, Nova Scotia, and then Canada, and the two columns are per day and per year. So we have relatively accessible data on the number of the cross sectional population, as I said before, so on any given day, how many people experience incarceration? So in Ontario, for provincial correctional facilities, and these data are from the 2018 2019 Statistics Canada Adult Correctional Statistics. So these don't reflect changes in the context of some decarceration with respect to the coronavirus pandemic. But but anyway, it gives you a rough sense. So for Ontario per day, we have about 7500 people in correctional facilities. And again, that's decreased during the coronavirus pandemic. But per year, in our provincial correctional facilities, it's about between 40 and 50,000. The data per year are not made accessible routinely through Statistics Canada, although Statistics Canada is currently doing work through a one of their routine correctional surveys to collect and share this information, but that won't be done for all provinces and territories, as well as for federal facilities at this time. I don't have the data on the number of people per day in Ontario in federal facilities. And again, that's just not easily accessible. For Nova Scotia, the cross sectional information for 2018-19 are that there are 468 people in correctional facilities. And I don't know if it's made routinely available, how many people are experiencing incarceration in provincial or federal facilities per year again, feel free to add that to the Q&A if you if you have that information and also what the sources because it's helpful for for us to understand what that looks like. And then for Canada, so again, the adult correctional statistics from Statistics Canada, the data on provincial facilities show that there are about 24,000 people in provincial and territorial facilities on any given day and about 14,000 in federal facilities on any given day. We don't know how many people across the country experience incarceration in provincial and territorial facilities. And from speaking with colleagues at the correctional service of Canada, there are probably about 20,000 people who experience incarceration per year in federal facilities. So here we are in Canada, we have amazing data systems. We can't say this is the number of people in our provinces in our in our territories and across the country that experience detention and incarceration per year, which to my mind is very problematic. Okay, so the second key message is that health status is worse for people who experience imprisonment compared with the general population. And when I talk about health status, absolutely that refers to the prevalence of disease, which is a more traditional way to look at health status. But it also includes a broader framing of what health means. And I like to use the definition from the World Health Organization, which is that health is a state of complete physical mental and social well being, and not near the merely the absence of disease or infirmity. So this is one of my two super overwhelming slides. And although I think there is some value to just being able to see everything altogether, and being able to look at the fact that the arrows up, which indicate that health is worse, are consistently pointing up across these domains of health. I also appreciate that this is challenging slide to interpret. So I'll take you through it line by line. So this is morbidity and mortality. So morbidity being indicators of disease, there's also some information on risk factors in here. And comparing with the general population. And this is research that is specific to Canada. But I think all of it is consistent across jurisdictions internationally, with some gaps and available data for sure. So starting on the first line. So if we look at, sorry, all cause mortality. So obviously morbidity isn't relevant here. But when we compare people who experience imprisonment to others who are matched by age and sex, we can see that within a given period, so this is a population based study that that I led using data from Ontario, all people admitted to provincial correction facilities, we follow them for 12 years. And we found that they were four times more likely to die within that time period compared to people in the general population with the same age and sex. The second line is infectious diseases. So we know that there is substantial overrepresentation of people with HIV and hepatitis C. And that's due to a variety of reasons, including the criminalization of illicit substance use, as well as the lack of access to harm reduction tools, both in the community and in prisons. And that leads to increased morbidity for HIV. The rates of the prevalence of HIV is five to 25 times higher in people in prisons compared to in the general population. And that the prevalence of hepatitis C is extremely high in this population with most recent Canadian estimates showing about 20% of people in our correctional facilities have hepatitis C infection. And also increased mortality associated with both these conditions. Moving to the third line, cancer. So we don't have a lot of evidence on this, but we have strong evidence that shows that incidents of lung cancer, liver cancer, cervical cancer and head and neck are increased and also that there's increased mortality associated with those four types of cancer. And just to think a little bit more specifically about why that might be, we know there's overrepresentation in this population of smoking, which is the strongest risk factor for lung cancer, with hepatitis B and hepatitis C, which contribute to liver cancer, and of HPV, human papillomavirus, which are the strongest risk factors for cervical cancer, as well as for head and neck cancer. Going down to the fourth line, so mental illness and substance use disorders. I think many of you will be familiar with this. We have substantial overrepresentation of people with various types of mental illness, including mood disorders like depression and bipolar disease, anxiety disorders, including post traumatic stress disorder, psychotic illness such as schizophrenia and personality disorders, as well as substance use disorders. And those are associated with substantial morbidity and substantial mortality. The next line is chronic diseases. So again, evidence of increased prevalence of diabetes and respiratory diseases, likely for the respiratory diseases also associated with smoking and injuries. So injury includes unintentional intentional sorry intentional and unintentional injuries. So intentional injuries would include injuries from assault. Sorry intentional injuries would include things like self harm and suicide and unintentional injuries would include things like overdose and experiencing assault. The the second to last row here reproductive health. So we've done some population health research that shows substantially increased adverse outcomes during pregnancy. And this is things like in infants having increased birth prematurely increased preterm birth, as well as being born small for the gestational age. So the stage of development of the infant. And also in increased rates of having to go to the ICU as a neonate. And then for women having increased rates of placental abruption where the placenta prematurely separates from the uterus, which of course has substantial risks for the infant. And the final line so again with this broad definition of health we think about determinants of health. So increased adverse childhood experiences such as experiencing violence and other forms of abuse and also witnessing domestic violence lower rates of sorry lower average levels of educational attainment lower rates of employment and lower income in people who experience incarceration compared to the general population. Now I should say of course that this is a very heterogeneous population. So all of these data I'm presenting represent a whole population perspective. So when we look at the whole population of people who experience imprisonment and compare it with the whole population of people who are others in the population, these are the trends we tend to see. Why is health worse in this population? So a few mechanisms. The first is that poor health can lead to imprisonment. So I've already mentioned criminalization of drug use which can lead to people who use drugs including people with substance use disorders being arrested and incarcerated. And also criminalization of mental illness. So people with poorly treated mental illness including psychotic illness might be more likely to experience incarceration. The second general mechanism is that imprisonment itself can exacerbate health and we see many people who experience imprisonment being imprisoned many times. People often will call our jails revolving doors and that can be directly that imprisonment exacerbates health. So for example people who go into correctional facilities tend to have an exacerbation of their mental health and that's both sort of positive mental health and also an exacerbation of mental illness. And people will often also experience harms from substance use including overdose or risk of infection from using and that can be superficial infections like cellulitis as well as blood-borne infections like HIV, hepatitis B and hepatitis C because of the lack of access to needle syringe exchange programs and other harm reduction opportunities. And imprisonment of course also impacts the factors that contribute to health and that as you can imagine being incarcerated for a period of days, weeks, months, years would impact your employment status and that's both if you have a job, your ability to maintain that job as well as your risk of or your ability to access a job once you have been incarcerated. Your housing status of course people aren't being paid well many people aren't being paid while they're incarcerated and so have real challenges with maintaining their housing status in the community while they're incarcerated and then relationships so the impact on people's children, people's partners, people's friends, neighbors, communities are substantial when someone goes into a correctional facility and there are undoubtedly other mechanisms also but those are just two that I think are particularly important when we think about the health status of this population. So how does the health of this population impact the general population? And again I've mentioned a couple of these already, disturbed social roles as I just was describing, transmission of communicable diseases so if people are having unprotected sexual contacts while they're incarcerated, whether consensual or non-consensual, if people are sharing needles as examples in correctional facilities of course when they are released back to the community they continue to carry those infections with them and then might be putting other people at risk of those infections also in terms of public safety so if we have people who are not accessing adequate treatment for mental illness there are risks in terms of violent behaviors that can result from that and then of course there are substantial costs to our healthcare and criminal justice system for people having poor health again leading to incarceration which is costly and then in terms of healthcare untreated healthcare or not treating health issues early on and ideally preventing health conditions from happening in the first place is less expensive than treating health conditions after they've arisen or after they have developed into more severed illness and this is a picture of a mural that I like to use because I think that while many of us are interested in addressing the health of people who experience imprisonment because it's important in and of itself I think there are benefits many benefits for our whole population in terms of addressing and redressing health issues in this population so closing the gap is good for Canada's health and it's a mural from the old Wellesley Hospital in Toronto okay key message number three healthcare access and quality are worse for people who experience imprisonment compared with the general population and this is true both while people are in custody and in the community and I've just chosen a few research or examples of research that I think illustrate this and and might be of interest to you so some of you will be familiar with this type of study which is an audit study which is frequently used in employment studies so people will either go for a job interview and have people for example people who have from different racial groups or send in CVs with names that suggest different ethnicities and see how that impacts their ability to access a job so in this study which was led by colleagues in BC our group was looking at access to primary care based on whether someone reported that they've been recently released from prison or not so within our research team some of the researchers called families physicians offices and this is based on offices that were listed on the BC I might not get the language exactly right but the BC medical society has a list of people who are open to new patients so called those offices and asked for an appointment and in the end they ended up contacting 250 offices and the caller either said I'm a person who's been recently released from prison and I'm looking for a family physician or I'm a person who's looking for a family physician and the outcome was whether that person was offered an appointment so the people you can see at the bar graph at the bottom the people who are for the people who are said that they've been recently released 43% were offered an appointment and that contrasts with 84% who said I'm looking for a family physician without saying that they had a history of recent release from prison 84% said that sorry in 84% of those calls people were offered an appointment so this is just one indicator of the experiences that people may encounter in the healthcare system and of course this is an artificial setup but I think nonetheless speaks to the fact that people who experience imprisonment experience discrimination within our healthcare system so this second slide is looking at pap testing for people in Ontario and looking at people who were experienced imprisonment in our provincial correctional facilities and those who did not and I think arguably pap testing might not be the number one priority for healthcare for people who experience incarceration but it is a fairly standard indicator of quality of care for females that we often use in primary care so I think it's important because it speaks to whether people are accessing preventive care so if you look at the bar graph this shows that well the darker bar is for people who are in the so-called prison group so people who experienced incarceration and the lighter bar is for people in the general population the rest of the general population I should say so if you look at the bars on the left the y-axis here is whether people are up to date for screening so at the moment of at the time of admission to jail for the prison group for the general population it's just at the analogous time 46% were up to date so for most people that would be having had a pap test within the previous three years and that contrast with people the people in the rest of the general population for whom 67% were up to date with respect to their pap testing so much lower rates of pap testing in the previous three years for people who experience incarceration compared to people in the general population we then followed the same group of people over the next three years and we could see that three years later 64% of people in the prison group were up to date with respect to their screening and and sorry let me elaborate that a little more so that's just following the people who who were not up to date three years later 64% of them were still sorry I'm getting myself in a muddle anyway so I'll start again so at the time of jail admission you can see that the rates of being up to date are much lower for people who are in the prison group compared to the general population and that's still the case three years later and and I think this is important because many of us who work in in correctional facilities are interested in what are the opportunities if people have to go to correctional facilities how can we use this as an opportunity to support people in meeting their health needs so this to me shows that although over time people do access preventive care there's still a gap where some people who are experiencing incarceration who might have been able to access this preventive care while in custody still are not accessing this preventive care and and this can be for various reasons people might be it's possible that people are being offered care and they're refusing it but nonetheless I think there is an unmet need here for preventive care and I think we need to be creative when we think about how can we help people meet their preventive care needs and how can we make it possible for people to access care in a way that's acceptable to them this is my second overwhelming slide so again with apologies and I'll speak through this in some detail okay so this is looking broadly at use of health care and this is following about 50 000 people who were released from Ontario provincial correctional facilities in 2010 okay so I'm going to talk you through each of these graphs and I'll just ask you to ignore the numbers for now so the first graph is ambulatory care so this is outpatient care so going to see a family physician would be an example of that but it could also be going to see a psychiatrist or a cardiologist or other people in other specialties so when we look at the gray bar the first gray bar so in prison you can see that comparing the time in prison to and this is following the same people over time looking at the time in prison and then following people at the first week after they're released and then the next three weeks and then a longer the next few months after that you can see that people are using more ambulatory care while they're in prison compared to in the community and we don't know why this is it's possible that people are using sort of following from the what I was describing before that people are using the time in prison to have their needs met and that would be I think great and appropriate use of healthcare in prison but of concern if this drops at the time of release when many people likely would need to access primary care for example to access prescriptions or to access various services and care needs given that this is a stressful time the concern is are there barriers to people accessing primary care and other kinds of outpatient care at the time of release and to follow from that if we look at the data for emergency department use we can see that comparing in prison to the week after release the rates of emergency department use go up substantially so to me this suggests a problem in terms of continuity of care what are the services what are the health issues that aren't being addressed which lead to people needing to use the emergency department so to me this is flagging a concerning issue which is that people aren't getting their needs met at the time of release and that's similarly reflected when we look at hospitalization in the bottom two graphs on the left we have hospitalization for medical or surgical reasons and then hospitalization for psychiatric reasons so again ignoring the numbers but just looking at the patterns we can see that from the time when people are in prison to the week after release you can see a substantial increase in terms of rates of hospitalization and to a large extent this is due to things like injury including unintentional injury like overdose and then if we shift over to psychiatric hospitalization we also see a substantial increase and the psychiatric hospitalization in particular is concerning to me because it's unlikely that within the first few days of release that people would decompensate so severely it's quite difficult to get admitted to hospital for psychiatric reasons in terms when I when I say that what I mean is you have to be very sick to be admitted to hospital for psychiatric reasons so to me this spike is very concerning and it could signal both that people are worsening substantially in terms of their psychiatric health it might also indicate that we are not providing access to hospitalization for people while they're incarcerated and I'll say that in a different way often as a family physician I see people in the correctional facility who are very ill from a psychiatric perspective and I think there's often a perception that people are accessing the care they need because they're in custody and they're not at risk of harming themselves or others because they're in custody but certainly incarceration does not provide the care and services that hospitalization provides for psychiatric reasons so again this pattern of a significant increase in hospitalization at the time of release is concerning and then just to show you broadly so the numbers here represent when we compare people who were released from provincial correctional facilities with people in the general population this is the number of times higher so for this one for ambulatory care while people were in prison they were 5.3 times as likely to use or they had rates of use of ambulatory care that were 5.3 times higher than age and sex matched people in the general population so you can see across all of these types of care people who experience incarceration have much higher rates of use compared to people in the general population and I'll just flag in particular for psychiatric hospitalization while in prison people are about 22 times as likely to be hospitalized for psychiatric reasons and then in the week after release 58 times as likely as people who are age and sex matched in the general population to be hospitalized for psychiatric reasons so to me this really speaks to the high amount of psychiatric morbidity and the severity of disease in this population okay so the fourth key message is that there are opportunities to improve health for people who experience imprisonment and I think this is really important for all of us who are concerned about the health of this population that we recognize that there are opportunities for each of us whether as physicians other health care providers lawyers or citizens to contribute to improving health for this population so I've sort of broadly sketched out opportunities to improve health there are many upstream strategies and that's not going to be what I'm focusing on but when I talk about upstream strategies basic things about people's life experiences that contribute directly or indirectly to them ending up in correctional facilities and that can be things like healthy childhoods so not experiencing abuse and childhood that can be some of our social policies what are our housing opportunities employment opportunities income opportunities that can sort of contribute to people having a healthier life and in turn not be not sort of following a trajectory that would lead to them experiencing incarceration or imprisonment and then there are downstream strategies so specific so I sort of screwed this into three areas which are my focus the first is preventing imprisonment the second is improving health care in jails and prisons and the third is supporting health on release so when I think about preventing imprisonment so this can be how do we make sure that people have access to high quality health care in the community so treatment for substance use disorders treatment for mental illness which is accessible and acceptable how do we dismantle the barriers to access and improve care so that people will access the care that they need to prevent imprisonment obviously there are many legal opportunities also so you know how can we improve access to drug courts being one example I'll pause there obviously there are people in this call who have much more expertise in this than I do the second area is improving health care in jails and prisons so as I said before if people must experience imprisonment in terms of that's our legal and policy context what can we do to support people meeting their needs while they're in jails and prisons and given that I'm a physician my focus is on health one of the areas that I think is important is improving health care while people are in jail and in prison and the third is how do we support health on release so the period of release to the community is associated with a lot of health risks including as I showed you in some of the date data high risks of emergency department use we also see high risk of mortality from overdose at the time of release and then more broadly obviously challenges in terms of reintegrating into families and communities accessing housing accessing employment and income supports how do we make sure that we have continuity of care and discharge planning and accessible community-based structures so that people are able to achieve health on release I just wanted to go into a bit more detail on the second which is improving health care in jails and prisons I think it's important to conceptualize I'll switch to the next slide what are we aiming for when we think about health care in prisons and again I think ideally we keep people out of prisons when possible if people have to go into health into prisons what should health care look like so I've put four possible ways to think about this here and I'll go into a bit more detail on each of them so we have international standards we could define standards based on federal and provincial or territorial legislation we can think about providing care that's equivalent to the care that's provided in the community and then we can think about providing care that's equitable to the care provided in the community so in terms of international standards so the Nelson Mandela rules also called the United Nations Standard Minimum Rules for the Treatment of Prisoners which are internationally agreed upon standards again this is for the minimum rule so this isn't supposed to be aspirational this is supposed to be like at least countries should be doing this so I just picked out a few of those rules to illustrate some of the challenges that we have so the first is health care services should be organized in close relationship to the general public health administration and in a way that ensures continuity of treatment and care including for HIV, tuberculosis and other infectious diseases as well as for drug dependence I think that we don't do this consistently well across jurisdictions in Canada and certainly the close relationship I think is inconsistent so this again international standards I think that there are major challenges to this within many jurisdictions in Canada looking at rule 25 so it says every prison shall have in place a health care service tasked with evaluating promoting protecting and improving the physical and mental health of prisoners paying particular attention to prisoners with special health care needs or with health issues that hamper their rehabilitation every jurisdiction in Canada of course has health care services within correctional facilities but I think that it's exceptional to have in place routine evaluation of those services to have in place strong health promotion services and to have a focus on improving health as opposed to preventing exacerbation again it's just and part of this is we don't know we don't have clear oversight in many jurisdictions in Canada regarding what's happening in terms of health care and then rule 33 the physician shall report to the prison director whenever he or she considers that a prisoner's physical or mental health has been or will be injuriously affected by continued imprisonment or by any condition of imprisonment in my experience most of the patients that I see experience adverse health impacts of incarceration so I mean this would mean almost routinely reporting this to the prison director and that's that's not consistently that's not the practice that's done so the second standard that we could think about is legislation so I've just pulled federal legislation here which is the corrections and conditional release act so again this is for people in federal correctional facilities and section 86 one again I don't have expertise and I have been challenged previously with interpretation of legislation but I think it's important to at least at a high level consider what the legislation says the correctional service of Canada shall provide every inmate with essential health care I don't find that a particularly accessible term and with reasonable access to non-essential mental health care that will contribute to the inmate's rehabilitation and successful reintegration into the community so again it's sort of two separate standards set one is for mental health care versus the rest of health care which isn't typically the way we approach health within health care in the community in general and also specifically the focus on rehabilitation and successful reintegration into community which I think is a problematic framing and then the third and fourth standards that we could look at community equivalence is language that's used quite frequently in United Nations documents as well as some work within Canada so equivalence typically is used to mean the same so we should be able to at least meet the same standards that are available in the community obviously this can be problematic because of the scale so prisons typically have a relatively small population compared to the general community so how do we manage to provide at least the same standard of care and then equity so as opposed to equivalence which usually means the same equitable would mean fair and in a way that will address any unfair health issues in this population so as an example one might say that equitable health care within prisons should have outstanding mental health care and substance use treatment in a way that is arguably superior to what's available in the community in order to be equitable and again I don't think we see that consistently across jurisdictions in Canada so I'm going to summarize the key messages first imprisonment is common in Canada I think given what most of you wrote in the poll you'll you'll agree that this is this experience is more common than many of us even those of us who have enough of an interest to participate in this session would think the second is that health status is worse for people who experience imprisonment compared to the general population third is that health care access and quality are worse and again this is overall for people who experience imprisonment compared with the general population and then fourth that there are many opportunities to improve health for people who experience imprisonment and I think that last point is the point that's most important which is that there are things that we can do across the areas upstream opportunities downstream opportunities including those three areas that I described how do we prevent incarceration prevent imprisonment how do we improve health care in our prisons and how do we support people as they transition back to the community so I will stop there and happy to hear any questions or comments you might have maybe I'll just leave it on the slide Martha should I hand it back to you sure all right okay so the first question that we have prison populations during the pandemic for various reasons for example reducing risk of transmission among people who are incarcerated and staff for leaving pressure from institutions given the barriers to health care access experienced by justice involved individuals that you've identified how would you advise governments looking to increase and accelerate releases how can we ensure continuity of care for justice involved individuals during COVID-19 when community resources are already overwhelmed and all right what's your response to that Fiona okay I'm just catching up I had to figure out how to stop sharing and okay so you're talking about the comment from E-MAT is that right yes okay sorry Martha I'm just going to reread it myself so call us within the prison populations I do think this is interesting we have a front page of the Herald today our Nova Scotia newspaper talks about conditions in the prisons right now here due to how COVID has caused extra constraints on well-being yeah I mean I can I can say a few things on this topic so the extent to which decarceration has been achieved during the coronavirus pandemic has varied substantially internationally and also across jurisdictions in Canada you know as someone who has followed this conversation nationally for many years where I think there has been to a large extent reluctance to agree that decarceration is possible and then in some jurisdictions we've seen 30 percent of people released and efforts to keep people from being admitted to correctional facilities in the first place I think this is important as an example of how when there is clear interest in achieving decarceration we can do this and E-MAT based on your comment it sounds like you're suggesting how do we do this increasingly and then how do we support people as they're released I mean this is a huge issue to give a concrete example housing I mean we can see our shelter system which obviously isn't the ideal form of housing that we want to transition people to but our shelter systems which are overwhelmed in the context of coronavirus and then if we have 30 percent of people in provincial correctional facilities being decarcerated within the period of several months shelters aren't able to absorb that kind of increased population size I think that the best thing we can do during COVID-19 but also routinely is to create strong relationships between our correctional facilities and community organizations to support people in meeting especially their urgent needs at the time of release and that's both I think because we have an obligation to do so and also it sets people up for success Martha I know that you've been you know actively involved in this area so I don't know if you want to add any you did have a very successful program back in April compared to across the country there was a 26 percent decarceration rate in provincial institutions and here in Nova Scotia we achieved 41 and that said it's all gone so the the that worked because there was funding set aside for Elizabeth Fry Coverdale and John Howard to support people who were coming out to house them as you said and to provide well not to provide the healthcare but to make sure they got the healthcare that they needed and that funding dried up at the end of the summer and so then we saw our provincial institutions fill back up so it does have to be a sustained arm of funding but it certainly didn't cause any harm to to reduce the prison population by 41 thanks for adding to that now the next question I'm abiding by the the voting so if you want something vote for it people the next question this is fascinating statistic I think this was early on that this was posted so it was probably about just how common incarceration is do you think this is a reflection of social structures leading to poverty and multiple contacts with the criminal system or to the over criminalization of stuff addiction medical health issues reproductive health etc what other factors may be contributing to this do these numbers make paramount reconceptualizing correctional facilities as key centers for healthcare delivery okay yeah I mean it's a it's a really important question or series of questions right I think that it does reflect our social structures for sure and as well as our like legal and policy context around how do people interact with criminal justice system all the way from police context through to who is incarcerated and for what sorry the order shifted um and and yeah I mean our incarceration rate certainly reflects many issues I mean the and the conceptualizing correctional facilities as a key center for healthcare delivery so a lot of people talk about this now in the context of de institutionalizations from psychiatric facilities which happened decades ago have our correctional facilities become the de facto place where people with severe mental illness access treatment which I think is something that most Canadians would not be comfortable with but when we look at the overall representation of people with severe mental illness not just one example substance use disorders is another in correctional facilities I think we see that happening so hang on it's um oh it's gone they're gone oh you clicked it to answered okay so what other factors yeah I mean I would say broadly we have like what's the baseline health status of the population which of course does reflect policy and the healthcare system and then we have our legal and policy context with respect to the criminal justice system and how those interact I think is what's what contributes to that but yeah this is a whole area that needs a lot of work for sure I think you're on mute Martha so the next question is from Dr. Wendy Norman thank you for this wonderful presentation I'm interested to hear your advice for the potential to improve strategies at the time of discharge to improve these transitions and perhaps establish or support continuity yeah I think um for any problem yeah some of this work needs to be done within correctional facilities so how do we make sure that everyone who is admitted to a correctional facility I mean similar to the way we do when people are admitted to hospital how do we start to plan for people's reintegration of the community how do we routinely identify and address the needs that people will have at the time of release and plan for those so is that housing is it access to primary care and and put those things in place and there are many challenges including that I think it's two-thirds of people in our provincial correctional facilities are on remand which means that they haven't yet been tried or sentenced so we don't know to a large extent when people are going to be released so it is challenging to plan for this but nonetheless we have to try to do that and and make it possible to have access to services in the community that can respond to needs on short notice so I guess both focusing on what we do within our correctional facilities and then what how the community develops its services to be able to attend to this population and that can be all kinds of things do we have access to child care while people are accessing health care or social services do we have access to getting a health insurance card for people who lose their ID how do we make primary care accessible and acceptable and I think there are specific examples of where this has worked that we need to learn from tailor scale up across jurisdictions so yeah that's this is the work we need to do yeah we do have a program here locally where people are met when they're released there's arrangements for a peer to meet them so that they have a companion they have a bus ticket the the basics that they might need as soon as they are released it's it's a very bare bones thing but it's enormously supportive I can just comment on that thanks Martha is um you know I think there have has been a lot of focus at least internationally possibly less so in Canada on sort of disease specific so okay your person with HIV in a correctional facility how do we support you attending to your need for care for HIV and I think just one other sort of plug for primary care is of course people are not just a single disease or risk factor or issue so I think we really need to think about how do we provide comprehensive care you know if people have to go see their probation parole officer twice a week how do we make it easy for them to also access primary care to address the their healthcare needs like how do we think about creative solutions that are person or patient focus depending on sort of the lens we're coming at this with so yeah very much agree Jocelyn can you speak to the status of COVID vaccine delivery in prisons how it's happening what lessons we can learn from the decision making about it so far and from the politicization of the issue and this has been a very differently approach between the pilot that the feds have launched this week versus what Doug Ford said in Ontario and so on so we've heard as of today that there's no plan yet for prisoners here in Nova Scotia yeah I mean I can't speak to this in detail just because I don't know the details of it like I read the announcement I guess it was early last week although I might have that a bit wrong that around the prioritization of people in federal correctional facilities of course people who are in congregate settings are at increased risk of acquiring transmission and acquisition of a respiratory illness such as coronavirus and and then in federal facilities in particular where the age distribution is such that people are older and there is increased morbidity then that puts people at increased risk of adverse consequences when they do get infected so you know I think arguably it makes sense to consider different populations of people in correctional facilities differently I don't know I haven't heard announcements from any provinces around similar decisions around people in provincial and territorial correctional facilities what lessons can we learn from the decision making so far I mean there isn't I don't know how much transparency there's been consistently but certainly when we think about principles around access to healthcare whether it's about vaccination or other aspects of healthcare you know the the fact is that incarceration or imprisonment is the punishment it's not that you're supposed to have worse access to healthcare as far as you're supposed to have well I went previously through what are people supposed to have in correctional facilities but certainly access to healthcare and vaccination would be included and part of that should be part of people's rights while they're in prison thank you so Korra McKenna from the coast what do you see as the media's role in this area what does the media often get wrong about this kind of data storytelling etc I don't know that I'm necessarily the best person to speak to this but I can say a few thoughts initial thoughts I mean the media play an important role in trying to share information about what is often a very close and inaccessible setting and we have seen the media take initiative in sharing information and stories you know at a high level I think federal correctional facilities have more or there's there's more information that's readily accessible from federal correctional facilities through things like the Office of the Correctional Investigator and we often see the media publicizing information that's shared through the Office of the Correctional Investigator or Correctional Service of Canada and then with respect to more broadly I think that the media will become involved when there is a story that is brought to light I don't I maybe Martha do you want to add to that I think that one of the things that I would really like to see the media do is to change the language and less use of these stigmatizing terms like inmate offender that to try to change the conversation and one of the challenges that the media experienced I do empathize is it's so difficult to talk to people inside it's so so difficult to get that side of the story and so to continue to push for access to those voices to that to that truth of the real experience and not and not always just be repeating for instance what Doug Ford said but anyway I deeply rely on the media for exposing what's happening the next question I'm just going to skip to somebody who hasn't been able to speak yet Michelle asks what is your opinion of decriminalization of all drugs love it I am I guess I don't have like a quick answer to that I am I'm interested in strategies that will reduce the adverse impacts of substance use on the health of people who use substances and I don't think that incarceration is working so I guess at a very basic level I think decriminalization makes sense the question was decriminalization of all substances maybe you haven't thought that through but have thought a bit more about opioids in particular and you know the really concerning consequences that I've seen in individual patients as well as in the research that I've done around dying from overdose in correctional facilities and and other harms that result from incarceration due to substance use yeah we have had several deaths here in Nova Scotia for and it's usually people who are very newly incarcerated and yeah and then in the post release period of course also so with some oh yes very elevation of risk at that point thank you so much for this insightful presentation can you touch on the importance of decolonizing healthcare education med school nursing school etc and how it's Eurocentric nature perpetuates harm on vulnerable populations big question I mean this is the work that we need to do right like we need to take major steps to like address the issues that we have in healthcare and of course that's true in correctional facilities where we see healthcare in correctional facilities where we see the over representation of people who are indigenous but it's also true about how we need to change healthcare in the community in order for people to be able to access acceptable high quality care and you know one of the strategies that we need to use to address that is through changing healthcare education yeah so if we have a problematic system and we keep doing the same thing it's not gonna change right so that's not super specific but but certainly I support efforts to move toward decolonization in healthcare and in education in particular Martha have you seen effective programs introduced at McMaster within the med school so I am I'm involved in specific initiatives in the medical school but I don't know what the ongoing work is I have been very impressed with the like level of engagement of trainees around initiatives both anti-racism work and decolonization work but I can't speak to specific initiatives I better move us along here and Janine asked do these stats include youth really good questions so just about the numbers earlier no almost everything I've presented is only for adults and in the international work around the health of people who experience um imprisonment there has been it has been identified that we don't know that much about youth I also know that the rates of imprisonment are much lower for youth as a consequence of criminal justice system reform apologies I don't know the details of this but maybe 15 years ago so and and my admittedly limited experience of visiting youth justice facilities is that compared with my experience with adult correctional facilities is that even when people do experience imprisonment it can be much more of a focus on rehabilitation and I'm not trying to at all glorify the correctional facilities for youth but more I think that there is a evidence informed approach which has led to decreasing incarceration rates as well as more focus on rehabilitation which is good direction to see things moving in yeah absolutely here in Nova Scotia we have vastly more restorative justice available to youth next question is amazing presentation so insightful this is actually more of a comment someone who works in federally funded halfway house I believe it is so important how these conversations and conduct this research so we can better serve the vulnerable populations that over represent our criminal justice system so thank you Caitlyn from Dell health related information available to the courts at time of sentencing and pre-sentence reports I find that these sources tend to be very incomplete and I'm concerned that my bail sentencing decisions are inadequate as a result are there better ways of getting courts access to health information for persons before the court so it's almost like are there health related gladu reports or cultural assessments like we see in pre-sentencing yeah I mean I guess we'll see a few things so I don't know the details of what this looks like certainly if a person consents to sharing their health records those can be shared with their representation and can be used to inform discussions around the case so I mean it might be a question of like should there be a routine assessment of whether that's appropriate because certainly you know the person owns their chart right so they should always have access to that information now that's not the same thing necessarily as having an assessment done because if someone isn't accessing healthcare you know for example if someone doesn't have a comprehensive assessment of their health status that doesn't really matter if you have access to their chart because it might not have the information that is needed to be able to bring the relevant information to the court proceedings but that would be an initial thought with the consent from the person to have access to their their chart information and then of course to request we often in the correctional facility or our work we often get requests from lawyers you know flagging an issue this person isn't getting the care they need or please prescribe this medication which of course is not an appropriate request but nonetheless it's something we can consider so yeah that that would be an initial an initial thought that's great I'm gonna have to end our question period now we're at 120 thank you so much for a fabulous discussion Dr. Kiyumjin and I want to thank everybody for participating we had a hundred people on this call so it was really wonderful and almost everybody was able to stay the whole time next no not next week in two weeks we will have Sharon Davis Murdoch as our speaker for the health law seminar and she will be speaking on the matters of black health resilience and determination I hope you can all join us then again same same idea Zoom with closed captioning so with that's all I have and I encourage everybody to look up Dr. Kiyumjin's work and continue to be interested in this this area thank you so much everybody goodbye so much bye


Hello, everybody. We are just starting now with Joe Conway giving a talk about getting into Postgres. And we're giving him one more applause, please. Thank you. So my name is Joe Conway, and I work for a company called Crunchy Data, mostly in the states, not here. I've been with the Postgres community. I've been using Postgres for like 20 years and started getting active with the community like 18 years ago, and I've been a committer for 15 years or so. These slides, there's a lot of detail on here. I'm going to go through them fairly quickly because that's the only way to do it in 50 minutes. They will be available if nowhere else on my own website, JoeConway.com. I'm not sure what the Postgres guys have coordinated for us to put slides somewhere centralized or not, but the wiki. So we still use the wiki? Okay. The wiki.postgres.sql.org, there's a page that has basically all the conferences you drill in and then on that each individual conference wiki page or links to the talks. So this talk is basically an introductory talk to help you get up and running on Postgres. If you're ready for me with Postgres, you probably don't need to be here. You might still learn something. I actually, even after all these years, whenever I sit down and prepare a talk like this, I learned something I didn't know. So it's, you know, it hopefully is worth your while. I'm going to start out with how you install Postgres. And one of the things you also see on these slides is that I'm trying to cover sort of the main ways to do things that are different between different distributions. So and with everything in Postgres, there's typically more than one way to do the same thing for the basic administrative type operations. So to start out when you install, you can get your RPMs or DEBS either from your own distribution or from the Postgres development group distribution. The RPMs managed by Debra, who's in the back of the room, and the DEBS are managed by Christoph Berg and some other folks who are wandering around, I'll pause down somewhere right now. There are also builds for Windows available. These blue on my slides, if you go download them later, are links to those spots on the website, so you can go find them. But there's at least a couple of different distributions of Windows binaries you can get. I don't necessarily recommend you run Postgres on Windows, but lots of people do for development purposes at least. Mac has options and Solaris and various BSDs have options. And I know I just said that this is kind of an introductory talk, but I'm going to spend just a couple of slides talking about how you might build and install Postgres from source, because that's kind of a useful skill to have. And it's actually surprisingly not that terribly difficult. It's, so I'm just getting started might sound scary, but it's not that hard. So my example for this is going to be on a Red Hat system. On a Red Hat or a variant system, you're going to need to install Git in order to get the Postgres source tree.   These are what I find I need on a kind of a clean Red Hat system in order to be able to build Postgres. So I'm not going to try and go through these in one by one in detail. But if you download the slides, you can get them from there. The next thing you're going to want to do is clone the Postgres source. So you typically want to make a spot where you're going to land that tree, switch into it and then use Git clone. And then this is the URL that you would use with Git clone. The clone Postgres from the main repository. And then CD into that directory and check out a branch. So in Postgres, the branches are named like this. REL, and actually prior to Postgres 10, this would be REL 9, underscore 6, underscore stable, or 9, underscore 5, underscore stable. 10 is the latest stable branch. So if you wanted to build the latest Postgres, this is the one that you would check out. Now, I'm assuming that we're on a system where we already have the Postgres development group RPMs installed on the system and I want to compile Postgres for debug build so I can troubleshoot something. So I want it to be configured basically the same way as what I get from the RPM. So what I'm going to do is capture and modify a couple of flags from the existing build from the RPM. So this command here is just, first I'm exporting the path to my binaries. And then I'm going to run pgconfigconfigure, which will get me exactly the way the RPMs were built. And I'm going to replace the optimization level and the debug flag that was used by the RPM maintainer. In order to use these flags instead, which are going to give me a more debuggable build. And then you just, once you've done that configure step, you just do make and make install this dash j on make, if you're not aware of it. That just does a parallel make, so it makes a lot faster. Once you've done that, kind of the typical debug workflow is you're going to log into PCQ or some client, you find the back end process associated with that client, usually you do that just on the server that Postgres is running on, use PS, identify the back end that the database that you're logged into. Attach to it with GDB and set a break point.  And again, someone was joking that the name of this talk was a crash course. So maybe we could do a whole course on this, but that's well beyond the scope of this thing. So, any questions about that before I move on? Okay, so the first step, now that you've got Postgres installed, you've probably installed it from a Debian package or an RPM as you want to configure it. So the files that are used for configuration are called postgresql.conf. And then there's also this postgresql.auto.conf. And that is actually generated when you do an ultra system command. So you wouldn't normally want to edit that one directly. The form of that file is normally a name of a configuration variable equals and then a value. And now, in order to activate the changes that you make, you're going to need to reload the Postgres service. And as I mentioned earlier, here's five different ways you can do that. Basically, there's a built-in command to Postgres called pgconf reload. If you select that, that's going to reload your configuration file. There's also a binary that comes with Postgres called pgcontrol. You can use that with a path to the data directory for the instance of Postgres you're interested in, and just the command reload. Or if you're using the service or the system control files, there's also equivalent ways to do that, service, Postgres reload, system control reload. I'm not sure why they switched these around, but that always bugs the heck out of me, I don't know about you. You can't have several services at the same time. Okay. And then finally, the really old school way to do this is you can send a SIGHUP signal to the Postmaster PID. So if you go find the actual Postmaster PID, Postgres spawns several processes when it starts up, even when nothing else is happening. But it's usually pretty easy to identify the one that is the Postmaster, because it's got the directory for the Postgres data directory in the command line. So if you find that one, you can actually send it a signal directly. Now if you want to make changes, you could basically have either persistent changes or you can have procession changes. The persistent changes are going to live across Postgres restarts, across Postgres session restarts, and so on. So the canonical way to do that is modifypostgresql.conf. You can also actually, the Postmaster itself, when it starts up, you can feed it options. And some of the startup scripts that are around do that. That's probably not something that you're going to mess with as someone new to Postgres, but that is something that you can do. And then in recent releases, I think it's been since about Postgres 9.4 or so. There's this command called alter system. So you can say alter system set or alter system reset with a particular configuration variable. And it'll make that change and it makes it persistent by using that postgresql.auto.conf that I was talking about earlier. This is something that a lot of people I think are not aware of, but there are other ways you can make persistent changes and bind them specifically. So you can alter a database and set a variable to be a certain value anytime someone logs into that database. Or you can do the same thing by altering a role. You can set a variable to be a certain value for a particular role anytime that role logs in. So for instance, if you've got a kind of a power user and you want to give them a higher work mem, you could do that. So every time they log in, they get a higher work mem, even than the one that's set for everyone else in postgresql.conf. The procession, you change configuration variables and procession using the set command. That's kind of the primary method. Reset will change it back to what it was originally when you started. Sure, we'll just show you what the value is. But again, there's five different ways here that you can see and change these things. There's a view called PG Settings, which will expose all of that. You can select from that view to look at the values. You can actually update that view in order to change the values. But just know that if you do that, that is just for the length of your session. As soon as you log out, that goes away. Nobody else sees the same changes. And that in and of itself can be very useful. There's also a function called CurrentSetting, which will give you a specific value for a specific variable. This can be really useful when you're doing very scripting. It can also be useful if you have a custom variable that you want to add to the environment and make use of in your own functions. And then the set config is a way that you can explicitly set a variable using a function. Okay, in terms of the actual settings for these configuration variables, there are people who do three hour tutorials on that topic alone. So there's no way to cover all that. I used to try and cover more of it in this talk and then I always ran out of time. So really, just keeping it to a couple of slides. These are kind of the ones that you'll almost certainly want to at least look at. The first one being listen addresses. Depending on the distribution you're on, that may be set to something like localhost. If it's set to localhost, no one can connect from off your box. So your client may not be able to connect until you've changed that to use either like asterisk which will mean all your interfaces or a specific interface on the host. So that's something you probably are going to want to change. You might not. You might only want to allow connections from localhost. Max connections defaults to 100. This one, the main thing I'll say about this is that don't make that any higher than you really need it. And if you find yourself wanting to make it like 500 or 1,000 then you should be looking at a connection pooler. Generally speaking, kind of rule of thumb, you don't want to have more than about two or two and a half times the number of CPUs in terms of actively running connections. Now you can have 100 open connections, 80 of which are doing nothing, and 20 that are active at any given time, that would be all right. But I still wouldn't leave 1,000 open connections even if only 20 of them were active because those all those connections use RAM to a certain degree. They use resources, they add, they increase the size of certain tables and share memory that Postgres uses. So generally make that setting as low as works for your environment. Shared buffers, this is the one that virtually I think everyone has probably heard of with Postgres shared buffers is what's used for caching data once it's been read from disk. There is a kind of a general thumb rule there that you use somewhere between a quarter and a half of RAM for shared buffers. But number one, that's a bit old guidance. And number two, it's very dependent on your situation. If you've got a Java app running on the same host as Postgres, maybe that's not a good thing to do because you need more room for Java. Kind of the best thing there is I could say is to test different settings for your workload and your data.   So that's something that you really wanna test with with your own workload. Workmem, now shared buffers is kind of a system wide. This is allocated once.  While they're all using the same pool, so it's not actually duplicated. Workmem is something that is used potentially multiple times within a given query. Postgres will use workmem to decide to build hash tables, basically. So if you've got aggregation going on, you might have hash aggregates. If you've got hash joins going on, there may be hash tables and memory for that. If you've got sorts going on, there may be memory usage for that. So you can have multiple times workmem just within a single session. So what you don't wanna do is set that to like a gigabyte and you've got 20 active sessions and they're all doing big queries. And now suddenly you're denial of servicing yourself because you're driving the server into a swap, right? So four megabytes is the default, that's really small. Again, depending on your workload, that might be just fine if you're doing OLTP-type stuff, it might be just fine. For some workloads, you might benefit by increasing that. I would just be careful about doing that. And again, as I mentioned earlier, you might bind that change to a specific user or specific database and not have it be global. Effective cache size is kind of a hint for the planner. The general guidance on that is to set it basically equal to the amount of RAM that is used by the OS for buffering files plus shared buffers. Again, that tends to be something like a half or three quarters of RAM in a lot of cases, but your mileage may vary. And then finally, I'll talk about this random page cost. This number four, basically there's a sequential page cost and a random page cost and the sequential page cost defaults to one, random defaults to four. And the idea is on average, it's about four times as expensive to read randomly than it is sequentially. And that was kind of developed. I think Tom Lane actually came up with that number. But he probably came up with it about 15 years ago on hardware of the day. So I find often that number in real life workloads, that number is too high and it drives bad plans. I usually end up setting that down to two and sometimes a little lower. You wouldn't want it ever to be below sequential page cost because that wouldn't make any sense. I have seen people try and do that.  The default does not give all that much information in the Postgres log. There's a lot of information available. At the very least, you're probably going to want to have a time stamp. So by changing this, you'll get these additional items added, appended to the, or prefix to the, to whatever gets logged in the Postgres log. Any questions about configuration settings before I move on? So you're asking if work mem will get released once whatever you're doing finishes. Memory structures that are using that memory definitely get released. They get, they get flushed. Postgres has kind of a, its own native garbage collection so that they will go away whether the OS actually releases it from the process immediately, I don't know. It'll shrink back, yeah. It's not, it's not holding on to that. So now I'm going to go on to the host-based authentication file. This is another critical file in the configuration of Postgres. It's usually to determine who can access the system. The way I like to think of this file is it's kind of like the set of firewall rules, almost. Basically, you're going to say, when a particular client tries to connect, if they match a set of rules on this line, this is the authentication scheme I'm going to use. Including you can have reject as one of those. And the first line that fires is the one that gets used. So if the person logging in fails to authenticate on that line, then they just won't be able to log into Postgres. It's not like it's going to move on, try another one. I'll show you an example of this in a minute. But basically, this is which hosts can connect from, how the client is authenticated, the user names that can be used, and which databases they can access. I didn't mention this earlier, but on a lot of my slides, you'll also see I put links right into the Postgres documentation for the topic that I'm talking about. So again, when you download the slides, you can drill in and get more details on these things. This file is read when the server is started, but it also needs to be reloaded for the changes to take effect if you edit it while Postgres is running. The first line matching the connection type, the address, the database, and the user is what gets used for the authentication. And if the line is picked and the authentication fails, the access is denied. And if no line matches, importantly, it fails to a deny. So this is what it looks like, the basic, this is right out of the file, the commented section that shows the examples. I'm going to cover a little bit what each of these things are in future slides. But this is the default on a Debian variant for Postgres 10. And this is the default for a Well7 install on Postgres 10. So you can see these local connections are basically for local host. And host means someone connecting not from local host. And no, excuse me, I should say these local is actually a local BSD socket connection. And host is a TCP connection, which can be from local host or it can be from another host. What you'll see here is on the Debian variants, when you're logged in using a local machine using a BSD socket, it actually uses peer. And the same thing on Red Hat, which effectively means it uses your OS authentication. So Postgres itself, by default, isn't going to ask for a password if you log in on one of these systems from the same machine. Something you might want to change. So first of all, the connection type. Local is the Unix domain socket. Host can either be a plain or an SSL encrypted socket. If you say host SSL, that means this particular line in HPAconf is requiring SSL. If you say host no SSL, it's requiring it not to be SSL. In terms of whether or not it matches. The database column, you can say all. So you can say that this rule applies to all databases. You can say the same user or same role, which basically means if the database name matches the user that's logging in. So it's kind of a shortcut. There's a replication. Because the all keyword does not actually work for the replication user, you have to specify a special role for the replication user. And then you can specify specific databases either as a list or as a list that's inside of a file that you include. User is very similar. You've got all is a wildcard for any user who should match this rule. You can have a list of user names. You can also have this plus and a group name, which means any roles that are in that group will match. And then you can have a list that's in a separate file that gets included. The addresses can either be an IP address with a CIDR mask or kind of a traditional IP address in a mask. You can also specify a host name. Or you can say these keywords same host or same net, which are, again, convenient shortcuts. If you only want to allow connections from the same host, you can use that keyword. Or if you only want to use allow connections from the same subnet, you can use the same net. The method is the authentication method that's going to be used when the rule is hit. So trust basically means just let the person in. Probably don't want that on other than development environments. The kind of built-in methods, starting in Postgres 10, there's something called Scram SHA-256. That's kind of not only meant to replace MD5. MD5 has been there essentially forever. But these days, in a lot of environments, MD5 is not considered to be secure. And so therefore not allowed. The password method is actually, I think, has been deprecated for a long time. And I think it's actually been removed in 10? Or is it 11 when it was removed? I think it was removed in 10. But in any case, for new installs, you would probably want to use Scram SHA-256. It's a much more secure mechanism for handling passwords. If you're not going to use that, you're going to use probably something like SIRT, which means I want to use SSL SIRT. So I can authenticate the server and the client of the server. I can also do peer, PAM, or IDENT. Don't necessarily recommend those in general. Probably more recommended if you can use an external system as something like Kobros or SSPPI Active Directory LDAP radius. Or you can explicitly say this rule should be rejected. And then each of those authentication methods might have some options associated with them. And they're just listed as name equals value. And it depends on which method, which options are allowed. So now here's an example. Again, here I'm saying from a Unix domain socket on the same machine, I'm just going to trust the connection. And from local hosts, I'm also going to trust the connection. But from someone on the same network, I'm going to require the Scram SHA-256. And someone from this specific network, I'm going to use LDAP. And so here's an example of options associated with LDAP. OK, any questions on that before I move on? No, that's all controlled through SQL commands. That's all in turn. I mean, I've seen people script that sort of thing where they reach out to LDAP and then have a cron job that goes out to LDAP, grabs some stuff, and then executes commands in the database. So you can kind of piece it together yourself, but that's not built in. OK, so now that we know how to set up all the configuration files for Postgres, the next thing you got to do is actually initialize the directory. Otherwise, you don't have a database to connect to. You can't even start. So again, multiple ways to do this. The process is called initDB. The binary that comes with Postgres, if you kind of do it yourself, is initDB. You give it this dash capital D in a data directory. And basically, it initializes that directory as your Postgres data directory. That has to be empty if it's not empty. You cannot exist, actually. It'll create it for you. But if it's there and it's not empty, you'll get an error. On Red Hat, they provide a script. Postgresql10 setup initDB is the command you would use. That creates a cluster and the config files all in a data directory in this location. So this is kind of the standard location on a Red Hat, CentOS system, varlib, PG SQL, version number, data. A Debian-based system, they also provide a script called create cluster, PG underscore create cluster. So in this case, PG underscore create cluster, 10 is my version and main is my instance. You can actually have multiple instances of Postgres of the same version on the same host, if you want, with the Debian packages. And so this is the way it's managed. That will create a cluster in varlib, Postgresql10 main, and that's the actual data directory, but it will put all the configuration files under etc. So it's etc, Postgresql10 main. And if you had another instance that wasn't called main, it was called base or something else, then that would just change. So once Postgres has been, the data directory has been initialized, the next thing you're gonna do is wanna start Postgres. Again, multiple ways to do that, depending on what system you're on. Just, this is a kind of a special note, if, again, this is kind of not something that you would do all the time, and you probably won't remember it, if the one thing you remember is that it exists when you need it, you'll know to go look for it. There is a single user mode, so if you use the Postgres binary dash dash single, point it to a data directory and a database name, it'll bring up Postgres in single user mode, and there are certain things that can really only be done in single user mode. Hopefully, you won't ever need that. It typically means something has gone wrong. So, yeah, I've seen people go years and years and years without ever needing that. However, as a developer at Postgres, sometimes I screw things up myself rather badly, so that's the way you can rescue a cluster in certain cases. Again, if you're gonna manually start Postgres, this PG control binary that comes with it, you give it the path to the data directory, you can optionally give it a log path and you tell it to start, and that'll start Postgres. What you probably wanna do if you're new to Postgres and you're probably running RPMs from your distribution or devs from your distribution is you're probably gonna use one of these methods. In Red Hat 6, it was still the old service style, so you would just say service Postgres QL start. Red Hat 7 is system D, so that's system control start Postgres QL dash 10. Debian comes with its own method for doing start, but the recent versions of Debian will also support the system D method. So you can use either the script that comes with the Debian distribution PG control cluster. So you say version 10, instance main, start. You can also use a command that's similar to this one. I think it's a little different in the name of the service. It's like Postgres QL at 10 dash main, I think, something like that. Stopping Postgres. If you're in single user mode, control D will get you out. If you do it manually, PG control, again, very similar to the start command, you're just gonna say stop. The difference here is you're gonna specify a method. I'll touch on what those methods are in another slide, but that is an important consideration. And that also may be a reason why you wanna use PG control instead of one of the services. In Red Hat 6, the old service model, it's just service Postgres stop. Red Hat 7 or system D, system control stop, service name, Debian base, same thing. PG control cluster, the version, the instance, and stop. Now, these shutdown modes are somewhat important. I think most of the distribution defaults, like the service files, use this fast method. If you use smart, basically Postgres is gonna sit there and wait until all of the existing sessions have closed. If you use fast, it's going to terminate all the existing sessions, but it's gonna do a graceful shutdown of Postgres. And what that means is it preserves things in a way when Postgres comes back up, it doesn't think that it crashed. Probably one of the more important aspects of that is that Postgres is constantly keeping stats about what activities are going on, but that staff file is not crash resistant. So if you do this last option immediate, basically, as far as Postgres is concerned, it just crashed or your server lost power or whatever. And when it comes back up, it has to go through a crash recovery and you're gonna lose your stats, which means now Postgres doesn't know when to auto vacuum your tables, at least not until the statistics start building up again. So you don't typically wanna do that. Another important aspect of that is recently ran into, if you're running Postgres, for instance, in a container, when the container goes down, if you don't arrange things just right, Postgres effectively gets killed. So now when it comes back up, let's say your database is on persistent storage, it's doing crash recovery just like I just described. So now let's talk about how to terminate a particular session. If you're in a terminal, you can find the process using PS or whatever, grep for maybe the database name. Once you find the PID, as in this case here, you can say kill sigterm with that PID and that will actually kill that back end process. There's a built-in way to do that. And this is more typical of what you'd probably wanna do on a running system. Let's say you run this SQL statement, you wanna look for the PID state and the clock time stamp minus the last time the state changed, which is an age. And the query from PGSTAT activity, this is one of the Postgres system views that's available that can show you. So in this case here, I've got this PID has been idle in transaction for 26 seconds and the command was begin. So someone ran it, started a transaction and never did anything.  So now I can say PG terminate back end with the PID number and kill it. Okay, it looks like I'm gonna have to speed up a little. I'm not gonna get through all this. You can do this more or less example, the same thing with cancel.  So you can do the same thing except it's sig end instead of sig term. And it's PG cancel back end instead of terminate back end. Okay, so now I'm onto the final section, just kind of miscellaneous hints that might help you get going. How many people in here have used PCQL before? Actually quite a few. So maybe this section isn't gonna be much of a surprise to anyone. So first of all, one thing to be aware of is you can specify host port and username when you run PCQL so you can connect to other systems. That's not always obvious to new users. I like to point that out. You can actually execute from, this is useful for dash scripts. If you run PCQL dash C, you can actually execute some SQL right from the command line. You can also do dash dash command. Or you can echo SQL into PCQL and execute that way. And there's actually subtle difference in the way these two are executed. When you do these two methods, if you've got multiple commands that are concatenated with a semi colon, they're basically sent to the Postgres all at once. And if one of them fails, kind of it just stops right there. Whereas if you do the echo into PCQL, it's more like you're executing from a file and it will go line by line. And so things like there's a option for PCQL called unerror stop. That works if you do the echo mode. It doesn't work if you do the other one. You can repeatedly execute a command using watch. This is really useful sometimes if you wanna keep an eye on something that's going on. So if you just use a watch command with one of these together, you can just see it refreshing on the screen. You can list all the databases using PCQL-L or dash dash list. This one is another one that's really useful. I'm continuing to be surprised how few people know this. This dash E or echo hidden, if you're only in PCQL when you do like slash D to describe a table and it shows you all about the table, PCQL executes a bunch of SQL in order to gather that information. If you ever wondered how to get that same information for yourself, start PCQL with the dash E and then do a slash D in a table name and you'll see all of the verbose SQL that's been run to gather that information. If you didn't already know it, basically PCQL as long as it's built with the read line supports, kind of normal read line things. So you can do up arrow for the last line in history. You can do control R and start typing the search history and you can do tab to autocomplete. Postgres community goes to great lengths to keep like that tab autocomplete stuff working with new features. These are some of the slash commands. Probably the most important one is slash question mark because that's gonna tell you what all the rest of them do. Slash H will actually give you help on a particular SQL command. DT will show you all the tables and you can optionally filter that including with wild codes. Slash D actually describes a table but you can also use wild cards there. Slash DF describes functions again with wild cards. Slash X is what's called expanded output and normally Postgres puts its output in a table that's wide, each column goes across. If that's too wide for your terminal slash X if you turn it on will actually cause the rows to be one, each column will be one under the other so it fits better on the screen. And in recent releases there's now an option to say auto which means Postgres, PCQL will actually just figure that out for you. Slash E lets you edit the query buffer and there's a built in now to PCQL slash watch which will repeatedly execute the same thing. Some general notes on syntax. With an identifier, Postgres if you don't quote your identifiers is gonna automatically downcase them so everything gets lower cased. If you want to preserve uppercase or if you wanna use keywords in the names of your objects or strange characters you can use quotes and then exactly whatever you type will be preserved. You can do embedded quotes using it by doubling them up. And you can actually do unicode identifiers using a kind of ugly syntax like this. There may be better ways to do it but it's not something I do all the time. So that was the best way I could come up with. String literals are similar so identifiers are things like table names, column names, other objects in the database. A string literal is something that you're storing in the table or comparing against or using in one of your functions. So the most simple form is basically just the single quotes around it. But there's something called dollar quoting I'm gonna cover in another slide which is very powerful and very useful. You can double up the quotes to have embedded quotes. And again you can do a literal unicode by appending this UAMP or SAM to the front. Comments in Postgres kind of centered SQL comments or dash dash. Postgres also supports C-style, multi-line comments, again very useful. Dollar quoting, so this is basically a method that you can have embedded quotes within string literals without driving yourself crazy doubling and quadrupling and years ago we sometimes had eight or 16 single quotes in a row. What you do is you have a dollar sign. It's gotta at least be two dollar signs but it can be two dollar signs with any tag in between. And as long as whatever you start with matches whatever you end with that's how Postgres will figure out everything in between is some kind of a string literal. And you can nest those things. So if you look at this example here I've got a function body which is delimited by dollar sign, underscore dollar sign. And inside of that function body I have a string literal which is now just double dollar, double dollar. And then inside of that string literal I can now use a single quotes and everything will just work. Okay, getting down to the end which is good since I'm now probably down to about five minutes. I'm just gonna cover the basic data types real quick. Character strings, there's text var car and car. These are kind of SQL standard, text is not. Bottom line here is they're, excuse me. As far as Postgres is concerned they're actually all stored exactly the same. So if you wanna maintain compatibility with other databases but I mean you use the SQL standard but there's really no particular benefit to doing that and so it's most people who use Postgres like all day long tend to use text. It might even be like probably not even measurably but arguably faster because these are doing extra checks that this one's not. Data types numbers, Postgres supports small ints, two bytes, four bytes, eight bytes. Serial is not a real data type. This is kind of a way to automatically add a sequence which can either be a four byte integer and eight byte integer. So serial and big serial is the eight byte integer. Real and double are both floating point. So these are kind of standard math library type things. Numeric is actually a arbitrary precision type of calculation. So if you need like exact precision you would wanna use numeric. That's gonna be take more room and be slower in general than using like double precision if you don't need it. So if you're doing, if you're collecting data from sensors you probably want double precision. If you're storing values that represent money you probably want numeric. If you're just creating a primary key column and you just want a number that's gonna sequence you want integer or big int. You probably want big int. There's not a lot of difference in performance actually if you use big ints versus integers that give you a whole lot more head room. So you could use big serial or just create a big int. Date time, we support date without time. We support time without date and with and without time zone. And then timestamp is basically a date and a time and we support those with and without time zone.  You probably in general wanna use timestamp with time zone and you intervals are very useful for a lot of things. There are enumeration types. This is kind of a way that you can build your own. I wanna constrain the values to be red, green, and blue. So now I can create this table clothes and I say I want my column color to be of type color and now I can specify that it's red and it'll be constrained to be one of those acceptable values. There are other ways to do this but this could be very useful at times. There are geometric types built into Postgres. You know, generally if you're gonna do geometric types you probably want to use PostGIS. That's a special extension. Again, we could spend all day talking about PostGIS. I'm not gonna try and cover it. Just know that PostGIS exists and if you've got spatial data, geometric type calculations you need to do, that's what you wanna go find. Postgres supports JSON. The JSON support is actually really good these days. There's actually a JSON and a JSON-B. JSON was the original one. This one basically checks well form this and preserves the string exactly the way it was when Postgres got it. Whereas JSON-B, the string is actually parsed and stored as a binary but the advantage to that is we have much more rich support for operators and indexes with JSON-B. So most of the time you probably wanna be using JSON-B if you're storing JSON. The one thing that you do have to be aware of is that it will mutate your input so what you get out may not look exactly like the input. There's an XML type, checks well form this, it does not do DTD validation importantly. And then finally some other miscellaneous data types. There's data type called byte A which is for binary data. There's a true Boolean. You can do arrays of pretty much all of the types. Multi-dimension arrays. You can do composites, in other words you can build tuples. You can actually have those be a column and a table or being used. You can actually build arrays of tuples like this. There are range types which are really good if you're doing scheduling type applications. You can say I want anything in this range or does this range overlap with that range. There's tech search types or there's support for full tech search which is really good in Postgres. And then there are other miscellaneous types for network addresses. There's lots of extensions that create types for Postgres. UIDs and here's a link for that. So I think I'm just about out of time and I'm done with the slides so there's maybe time for one question. Hello, thank you. So this is when you've got a large buffer in. So when you insert in the P-SQL and you're missing some character or something in the SQL statement, you're missing a quarter of a parentheses. And so what is it you want to be able to do? So it's there in your text editor when you paste it in the P-SQL it's not there. What are you using like a terminal client? I mean that almost sounds like it might be a bug in the terminal's paste buffer. I'm not sure I've seen that. I think that's it. That's it. Thank you very much.


So the first thing that you want to do is collect a list of email sources The list of email sources will include things like office 365 of course because that's where your email is hosted But it may also include things like email marketing services. So if you're using things like Mailchamp or constant contact We would need to collect information about where those you know emails are being sent or how those emails are being sent Specifically, we would have to get something called an include from those sources. So if we went and looked online for The Mailchamp SPF include for example, we would get a portion of the record that we would need to add to our own SPF record It would also include things like CRM services or are you using Salesforce or Dynamics things like that. We would need those sources if you send emails from your own website So if you have an SMTP service on your own website that sends email directly from your web server Then you wouldn't need to include the IP address of where your web server is hosted And basically anything else that is sending email as your domain We need to collect all of the information about those things so that we know Where email legitimate email is being sent from as your domain


My name is Anu and I'm a third year physiotherapy student from Estonia. And I'm a second year physiotherapy student at Saxon University of Applied Sciences. Welcome to the Digital Discovery Day. Welcome to the Digital Discovery Days, which is an online event where you can experience Saxon and all its courses from the comfort of your home. This day is very special because you can find out everything that you want about your potential program. And there will be students and lecturers who will answer your questions in the best way possible. You can discover this and much, much more during the Digital Discovery Day. If you would have questions about a particular study course, you can go to InToSaxon.com, type your questions there and our students will answer them for you. I would like to wish you all good time, get ready and enjoy a Digital Discovery Day. Do we? Joe Adios.