Re: troubleshooting postgresql ldap authentication

Hi Chris, > On 08. Jun, 2020, at 23:05, Chris Stephens wrote: > posgresql 12 > centos 7 here: PostgreSQL 11.6 & 12.3, SLES 12 & 15 > hostsslall all 0.0.0.0/0 ldap > ldapserver="ldaps://xxx" ldapbasedn="yyy" ldaptls=1 we use: hostssl all all 0.0.0.0/0

Re: Something else about Redo Logs disappearing

Adrian Klaver writes: > On 6/8/20 7:33 PM, Peter wrote: >> That "cp" is usually not synchronous. So there is the possibility >> that this command terminates successfully, and reports exitcode zero >> back to the Postgres, and then the Postgres will consider that log >> being safely away. > Which

Re: Something else about Redo Logs disappearing

On 6/8/20 7:33 PM, Peter wrote: Actually, the affair had some good side: as usual I was checking my own designs first and looking for flaws, and indeed I found one: If you do copy out the archive logs not directl

Re: Something else about Redo Logs disappearing

Actually, the affair had some good side: as usual I was checking my own designs first and looking for flaws, and indeed I found one: If you do copy out the archive logs not directly to ta

Re: 12.2: Why do my Redo Logs disappear??

On 6/8/20 6:38 PM, Peter wrote: On Mon, Jun 08, 2020 at 05:40:20PM -0700, Peter Geoghegan wrote: And now for the nitpicking part :) On Mon, Jun 08, 2020 at 05:35:40PM -0700, Adrian Klaver wrote: ! > I am doing RedoLog Archiving according to Docs Chapter 25.1. ! ! There is no ReDo logging, t

Re: 12.2: Why do my Redo Logs disappear??

On Mon, Jun 08, 2020 at 05:40:20PM -0700, Peter Geoghegan wrote: ! ! I strongly suspect that you were hit by the bug fixed in commit ! 4e87c483. You should upgrade to Postgres 12.3 ASAP, to get that fix: ! ! "Avoid premature recycling of WAL segments during crash recovery ! (Jehan-Guillaume de Ro

Re: troubleshooting postgresql ldap authentication

On Tue, Jun 9, 2020 at 9:05 AM Chris Stephens wrote: > hostsslall all 0.0.0.0/0 ldap > ldapserver="ldaps://xxx" ldapbasedn="yyy" ldaptls=1 > does anyone know what might be causing "LDAP: Bad parameter to an ldap > routine" You probably want ldapurl="lda

Re: 12.2: Why do my Redo Logs disappear??

On Mon, Jun 8, 2020 at 5:17 PM Peter wrote: > Loosing a RedoLog is very bad, because there is no redundancy, > loosing a single one of them makes the timeline disappear and it > will only reappear after another Base Backup. Very very bad. >In this case, it seems, Postgres will delete the cur

Re: 12.2: Why do my Redo Logs disappear??

On 6/8/20 5:02 PM, Peter wrote: Hi all, this is a 12.2 Release on FreeBSD 11.3. I am doing RedoLog Archiving according to Docs Chapter 25.1. There is no ReDo logging, there is WAL logging. What docs, because section 25.1 in the Postgres docs is : https://www.postgresql.org/docs/12/backup-

Re: Is it possible to use keywords (date units) in a function definition?

On Mon, Jun 8, 2020 at 5:06 PM Martin Gainty wrote: > CREATE OR REPLACE FUNCTION DATETIME_DIFF(end TIMESTAMP(3), start > TIMESTAMP(3), datepart UNIT) RETURNS DOUBLE PRECISION AS $$ > Duplicate email from account (same sender) - already answered on the original/correct thread. David J.

12.2: Why do my Redo Logs disappear??

Hi all, this is a 12.2 Release on FreeBSD 11.3. I am doing RedoLog Archiving according to Docs Chapter 25.1. During the last week I have lost 4 distinct Redo Logs; they are not in the backup. Loosing a RedoLog is very bad, because there is no redundancy, loosing a single one of them makes the

Is it possible to use keywords (date units) in a function definition?

Hello, I recently tried to write a wrapper function to calculate the difference between two dates, mainly as a convenience. I'd essentially be emulating EXTRACT( FROM date1 - date2), in various ways. I got a bit stuck on allowing specification of the : is this possible in function definitions?

Re: Is it possible to use keywords (date units) in a function definition?

On Mon, Jun 8, 2020 at 2:57 PM Alistair Johnson wrote: > Hello, > > I recently tried to write a wrapper function to calculate the difference > between two dates, mainly as a convenience. I'd essentially be emulating > EXTRACT( FROM date1 - date2), in various ways. I got a bit stuck > on allowing

Re: "INSERT ON CONFLICT UPDATE" - Use of indexes ?

On Tue, 9 Jun 2020 at 00:42, Laura Smith wrote: > What'st the current state of play with indexes and ON CONFLICT ? The docs > seem to vaguely suggest it is possible, but this SO question > (https://stackoverflow.com/questions/38945027/) seems to suggest it is not. > > I've got a unique named in

Re: Is it possible to use keywords (date units) in a function definition?

On 6/8/20 3:10 PM, David G. Johnston wrote: On Mon, Jun 8, 2020 at 2:57 PM Alistair Johnson > wrote: RETURN EXTRACT(datepart FROM end - start); Any ideas? Is this even possible? Use the "date_part" function. That would work on the Postgres side, but the OP also

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

On 6/8/20 3:31 PM, Ron wrote: On 6/8/20 9:23 AM, Thorsten Schöning wrote: Guten Tag Ron, am Montag, 8. Juni 2020 um 15:35 schrieben Sie: Would a set of GLOBAL temporary tables be a better fit for your problem (eliminating the need to create temporary tables each time)? Do you mean plain table

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

On 6/8/20 9:23 AM, Thorsten Schöning wrote: Guten Tag Ron, am Montag, 8. Juni 2020 um 15:35 schrieben Sie: Would a set of GLOBAL temporary tables be a better fit for your problem (eliminating the need to create temporary tables each time)? Do you mean plain tables simply created using CREATE T

Re: Is it possible to use keywords (date units) in a function definition?

On Mon, Jun 8, 2020 at 2:57 PM Alistair Johnson wrote: > > RETURN EXTRACT(datepart FROM end - start); > Any ideas? Is this even possible? > Use the "date_part" function. David J.

Is it possible to use keywords (date units) in a function definition?

Hello, I recently tried to write a wrapper function to calculate the difference between two dates, mainly as a convenience. I'd essentially be emulating EXTRACT( FROM date1 - date2), in various ways. I got a bit stuck on allowing specification of the : is this possible in function definitions? I'd

Re: When to use PARTITION BY HASH?

On Tue, 9 Jun 2020 at 01:07, Ron wrote: > > On 6/8/20 3:40 AM, Oleksandr Shulgin wrote: > [snip] > > I've found the original commit adding this feature in version 11: > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1aba8e651ac3e37e1d2d875842de1e0ed22a651e > It says: > > "Hash par

Re: Index no longer being used, destroying and recreating it restores use.

On Mon, Jun 8, 2020 at 2:34 PM Koen De Groote wrote: > So, this query: > > select * from item where shouldbebackedup=true and > itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by > filepath asc, id asc limit 100 offset 10400; > > Was made into a function: > > create or repl

troubleshooting postgresql ldap authentication

posgresql 12 centos 7 i am trying to configure ldap authentication. i have the following pg_hba.conf entry (server and basedn are correct but not shown): hostsslall all 0.0.0.0/0 ldap ldapserver="ldaps://xxx" ldapbasedn="yyy" ldaptls=1 when i attempt to

Re: Index no longer being used, destroying and recreating it restores use.

So, this query: select * from item where shouldbebackedup=true and itemCreated<='2020-06-05 00:00:00.000' and backupperformed=false order by filepath asc, id asc limit 100 offset 10400; Was made into a function: create or replace function NeedsBackup(text, int, int default 100) returns setof ite

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

Guten Tag Thorsten Schöning, am Montag, 8. Juni 2020 um 10:14 schrieben Sie: > When the table needs to be created, when is it visible to other > threads using the same transaction, before or after executing the > additional query? There is a misconception here: Multiple concurrent exec doesn't se

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

Guten Tag Ron, am Montag, 8. Juni 2020 um 15:35 schrieben Sie: > Would a set of GLOBAL temporary tables be a better fit for your problem > (eliminating the need to create temporary tables each time)? Do you mean plain tables simply created using CREATE TABLE or is there some additional concept of

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

Guten Tag Daniel Verite, am Montag, 8. Juni 2020 um 12:07 schrieben Sie: > But a SQL session on the server takes its statements from a FIFO queue > and processes them serially, so there's no intra-session concurrency. > In fact multi-threaded SQL clients *must* make sure that they don't > send con

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

On 6/8/20 3:14 AM, Thorsten Schöning wrote: Hi all, I have an app exposing web services to generate reports. Those web services accept multiple reports per request and calculate them concurrently. There's one transaction spanning each entire request and used by ALL spawned threads. The app makes

Re: "INSERT ON CONFLICT UPDATE" - Use of indexes ?

Laura Smith schrieb am 08.06.2020 um 14:42: > Hi, > > What'st the current state of play with indexes and ON CONFLICT ?  The docs > seem to vaguely suggest it is possible, but this SO question > (https://stackoverflow.com/questions/38945027/) seems to suggest it is not. > > I've got a unique named

Re: "INSERT ON CONFLICT UPDATE" - Use of indexes ?

On 6/8/20 5:42 AM, Laura Smith wrote: Hi, What'st the current state of play with indexes and ON CONFLICT ?  The docs seem to vaguely suggest it is possible, but this SO question (https://stackoverflow.com/questions/38945027/) seems to suggest it is not. I've got a unique named index on a tabl

Re: When to use PARTITION BY HASH?

On 6/8/20 3:40 AM, Oleksandr Shulgin wrote: [snip] I've found the original commit adding this feature in version 11: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1aba8e651ac3e37e1d2d875842de1e0ed22a651e It says: "Hash partitioning is useful when you want to partition a growing

"INSERT ON CONFLICT UPDATE" - Use of indexes ?

Hi, What'st the current state of play with indexes and ON CONFLICT ?  The docs seem to vaguely suggest it is possible, but this SO question (https://stackoverflow.com/questions/38945027/) seems to suggest it is not. I've got a unique named index on a table (i.e. "create unique index xyz...") b

Re: Postgres 12 RLS

On Monday, 8 June 2020 12:42, Paul Förster wrote: > Hi Laura, > > > On 08. Jun, 2020, at 12:46, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch > > I had a lightbulb moment just now and tried that, but it doesn't seem to be > > working. > > The app returns "pg_execute(): Query failed: ERROR: p

Re: Postgres 12 RLS

Hi Laura, > On 08. Jun, 2020, at 12:46, Laura Smith > I had a lightbulb moment just now and tried that, but it doesn't seem to be > working. > > The app returns "pg_execute(): Query failed: ERROR: permission denied for > table" > > This is despite me: > • Changing to SECURITY INVOKER on

ts_debug() style functions for jsonpath debugging

Hello everyone, I am working with jsonpaths heavily and was wondering if there is any method for debugging a jsonpath expression in a similar method to ts_debug() for text search? Essentially what I would like to do is debug the splitting of a path into tokens or logical components using the s

Re: Postgres 12 RLS

On Monday, 8 June 2020 11:25, Paul Förster wrote: > Hi Laura, > > > On 08. Jun, 2020, at 12:17, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch > > $$ LANGUAGE plpgsql SECURITY DEFINER; > > you might want to use security invoker instead of definer. > > https://www.postgresql.org/docs/current/sq

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

> That's what I understood as well, but I'm creating those concurrently > WITHIN one and the same session and transaction. :-) Did I interpret this as "two different sessions via application threads within the same transactions of PG". Does the thread create its own PG session for each thread or

Re: Postgres 12 RLS

Hi Laura, > On 08. Jun, 2020, at 12:17, Laura Smith > $$ LANGUAGE plpgsql SECURITY DEFINER; you might want to use security invoker instead of definer. https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY https://www.cybertec-postgresql.com/en/abusing-sec

Postgres 12 RLS

Hi, I'm having a little trouble with RLS in Postgres 12, although first time I've used RLS, so it might just be me ! The problem is that I am calling a function from a web-app, but the function seems to be executing as "postgres" even thouhg the web-app logs in as a completely different role ?

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

Thorsten Schöning wrote: > > The caveat you mention about IF NOT EXISTS does not apply to > > temporary tables, as they're not shared across sessions.[...] > > That's what I understood as well, but I'm creating those concurrently > WITHIN one and the same session and transaction. :-) Bu

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

Guten Tag Daniel Verite, am Montag, 8. Juni 2020 um 11:05 schrieben Sie: > The caveat you mention about IF NOT EXISTS does not apply to > temporary tables, as they're not shared across sessions.[...] That's what I understood as well, but I'm creating those concurrently WITHIN one and the same ses

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

Thorsten Schöning wrote: > I'm using "CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]" and the > associated queries can take a long time. So the following lists some > questions about executing those concurrently, even thouzgh I've > already read threads like the following: > > The bottom

Re: When to use PARTITION BY HASH?

On Sat, Jun 6, 2020 at 6:14 PM Michel Pelletier wrote: > > Well lets take a step back here and look at the question, hash > partitioning exists in Postgres, is it useful? While I appreciate the need > to see a fact demonstrated, and generally avoiding argument by authority, > it is true that man

Re: checking existence of a table before updating its SERIAL

Matthias Apitz schrieb am 08.06.2020 um 09:53: > We're updating the SERIAL of a bunch of tables with a SQL script which > does for any table: > > /* table: idm_tasktab */ > DO $$ > DECLARE > max_id int; > BEGIN > SELECT INTO max_id GREATEST(COALESCE(max(taskid), 0),0) + 1 FROM > idm_tasktab; >

Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

Hi all, I have an app exposing web services to generate reports. Those web services accept multiple reports per request and calculate them concurrently. There's one transaction spanning each entire request and used by ALL spawned threads. The app makes sure that e.g. committing transactions is ha

Re: Multitenent architecture

Hi All, Thanks a lot for information, I will look into it and get back to you. Regards, Vasu Madhineni On Sun, Jun 7, 2020 at 1:21 AM Michel Pelletier wrote: > > On Sat, Jun 6, 2020 at 3:14 AM Vasu Madhineni > wrote: > >> Hi Rob, >> >> Our environment is medical clinical data, so each clinic

Re: checking existence of a table before updating its SERIAL

On Monday, June 8, 2020, Matthias Apitz wrote: > > Can some kind soul help me with doing a test for the existence of the > table to avoid the error message about non existing relation? > https://www.postgresql.org/docs/12/catalogs-overview.html David J.

checking existence of a table before updating its SERIAL

Hello, We're updating the SERIAL of a bunch of tables with a SQL script which does for any table: /* table: idm_tasktab */ DO $$ DECLARE max_id int; BEGIN SELECT INTO max_id GREATEST(COALESCE(max(taskid), 0),0) + 1 FROM idm_tasktab; RAISE NOTICE '% % %', 'idm_tasktab', 'taskid', max_id ;