Re: GPG signing

Marc Munro writes: > I need to be able to cryptographically sign objects in my database > using a public key scheme. > > Is my only option to install plpython or some such thing? Python > generally makes me unhappy as I'm never convinced of its stability or > the quality of its APIs, and it

Re: GPG signing

Marc Munro wrote: > On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote: > > On 5/26/20 12:01 PM, Marc Munro wrote: > > > I need to be able to cryptographically sign objects in my database > > > using a public key scheme. > > > [ . . . ] > > > Any other options?  Am I missing something? > > >

Re: GPG signing

On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote: > On 5/26/20 12:01 PM, Marc Munro wrote: > > I need to be able to cryptographically sign objects in my database > > using a public key scheme. > > [ . . . ] > > Any other options?  Am I missing something? > >

Re: GPG signing

On Tuesday, May 26, 2020, Marc Munro wrote: > I need to be able to cryptographically sign objects in my database > using a public key scheme. > > Any other options? Am I missing something? > This feels like it should be an application (middleware...) concern, not the database proper. i.e.,

Re: GPG signing

On 5/26/20 12:01 PM, Marc Munro wrote: I need to be able to cryptographically sign objects in my database using a public key scheme. Is my only option to install plpython or some such thing? Python generally makes me unhappy as I'm never convinced of its stability or the quality of its APIs,

GPG signing

I need to be able to cryptographically sign objects in my database using a public key scheme. Is my only option to install plpython or some such thing? Python generally makes me unhappy as I'm never convinced of its stability or the quality of its APIs, and it is not obvious to me which of the

Re: Advise on how to install pl/perl on existing DB.

On 5/26/20 9:32 AM, David Gauthier wrote: psql (9.6.0, server 11.3) linux Hi: I'm a PG users who has asked our IT team to install pl/perlu on an existing 9.6.0 instance on linux.  They really don't know how to approach this.  Could someone point me to a good step-by-step (including ptrs to

Re: Advise on how to install pl/perl on existing DB.

On Tue, 2020-05-26 at 12:32 -0400, David Gauthier wrote: > psql (9.6.0, server 11.3) linux > > Hi: > I'm a PG users who has asked our IT team to install pl/perlu on an > existing 9.6.0 instance on linux. They really don't know how to > approach this. Could someone point me to a good

Advise on how to install pl/perl on existing DB.

psql (9.6.0, server 11.3) linux Hi: I'm a PG users who has asked our IT team to install pl/perlu on an existing 9.6.0 instance on linux. They really don't know how to approach this. Could someone point me to a good step-by-step (including ptrs to any downloads they may need) ? Also, when they

Re: Help understanding SIReadLock growing without bound on completed transaction

On second look, it does seems the xid crossed the 2^32 mark recently, since most tables have a frozenxid close to 4b and the current xid is ~50m: SELECT relname, age(relfrozenxid), relfrozenxid FROM pg_class WHERE relkind = 'r' and relname not like 'pg%' order by relname; relname  |   

Re: Help understanding SIReadLock growing without bound on completed transaction

On Fri, May 22, 2020 at 3:15 PM, Thomas Munro < thomas.mu...@gmail.com > wrote: > > > > Predicate locks are released by ClearOldPredicateLocks(), which releases > SERIALIZABLEXACTs once they are no longer interesting. It has a > conservative idea of what is no longer interesting: it waits

Re: pg_basebackup + incremental base backups

Greetings, * Christopher Pereira (krip...@imatronix.cl) wrote: > On 24-May-20 15:48, Stephen Frost wrote: > >That really shouldn't be possible. I'm very curious as to exactly what > >happened that resulted in your primary/replica being 'out of sync', as > >you say. > > Actually this was more a

Re: Slow SELECT

On 2020-05-26 1:45 PM, David Rowley wrote: On Tue, 26 May 2020 at 22:31, Frank Millman wrote: Thank you David. I tried that and it produced the correct result in 53ms, which is what I am looking for. It will take me some time to understand it fully, so I have some homework to do! The

Re: Slow SELECT

On Tue, 26 May 2020 at 23:41, Vik Fearing wrote: > > On 5/26/20 12:04 PM, David Rowley wrote: > > Since "my_table" is small, you'd likely be much better doing a manual > > rewrite of the query to join a subquery containing the required > > details from "my_table". It looks like you want the

Re: Slow SELECT

On Tue, 26 May 2020 at 22:31, Frank Millman wrote: > Thank you David. I tried that and it produced the correct result in > 53ms, which is what I am looking for. > > It will take me some time to understand it fully, so I have some > homework to do! The main problem with your previous query was

Re: Slow SELECT

On 5/26/20 12:04 PM, David Rowley wrote: > Since "my_table" is small, you'd likely be much better doing a manual > rewrite of the query to join a subquery containing the required > details from "my_table". It looks like you want the row_id from the > latest tran_date for each fld_N column. So

Re: Slow SELECT

On 2020-05-26 12:04 PM, David Rowley wrote: On Tue, 26 May 2020 at 19:23, Frank Millman wrote: The table sizes are - my_table : 167 rows table_1 : 21 rows table_2 : 11 rows table_3 : 3 rows table_4 : 16 rows Therefore for each tran_date in my_table there are

Re: Slow SELECT

On 2020-05-26 12:02 PM, Christian Ramseyer wrote: Hi On 26.05.20 09:22, Frank Millman wrote: I have looked at the EXPLAIN, but I don't really know what to look for. I can supply it if that would help. My favorite approach to tuning Postgres queries is: 1. Run EXPLAIN ANALYZE 2.

Re: Slow SELECT

On 2020-05-26 11:27 AM, Charles Clavadetscher wrote: On 2020-05-26 11:10, Charles Clavadetscher wrote: Hello On 2020-05-26 10:38, Frank Millman wrote: On 2020-05-26 9:32 AM, Olivier Gautherot wrote: Hi Frank, On Tue, May 26, 2020 at 9:23 AM Frank Millman >

Re: Slow SELECT

On Tue, 26 May 2020 at 19:23, Frank Millman wrote: > The table sizes are - > my_table : 167 rows > table_1 : 21 rows > table_2 : 11 rows > table_3 : 3 rows > table_4 : 16 rows > > Therefore for each tran_date in my_table there are potentially > 21x11x3x16 = 11088 rows.

Re: Slow SELECT

Hi On 26.05.20 09:22, Frank Millman wrote: > > I have looked at the EXPLAIN, but I don't really know what to look for. > I can supply it if that would help. > My favorite approach to tuning Postgres queries is: 1. Run EXPLAIN ANALYZE 2. Copy/Paste the output into the fantastic

Re: Slow SELECT

On 2020-05-26 11:10 AM, Charles Clavadetscher wrote: Hello On 2020-05-26 10:38, Frank Millman wrote: On 2020-05-26 9:32 AM, Olivier Gautherot wrote: Hi Frank, On Tue, May 26, 2020 at 9:23 AM Frank Millman > wrote:     Hi all     I have a SELECT that runs over 5

Re: Slow SELECT

On 2020-05-26 11:10, Charles Clavadetscher wrote: Hello On 2020-05-26 10:38, Frank Millman wrote: On 2020-05-26 9:32 AM, Olivier Gautherot wrote: Hi Frank, On Tue, May 26, 2020 at 9:23 AM Frank Millman > wrote: Hi all I have a SELECT that runs over 5

Re: Slow SELECT

Hello On 2020-05-26 10:38, Frank Millman wrote: On 2020-05-26 9:32 AM, Olivier Gautherot wrote: Hi Frank, On Tue, May 26, 2020 at 9:23 AM Frank Millman > wrote: Hi all I have a SELECT that runs over 5 times slower on PostgreSQL compared with Sql

Re: Slow SELECT

On 2020-05-26 9:32 AM, Olivier Gautherot wrote: Hi Frank, On Tue, May 26, 2020 at 9:23 AM Frank Millman > wrote: Hi all I have a SELECT that runs over 5 times slower on PostgreSQL compared with Sql Server and sqlite3. I am trying to understand why.

Re: Slow SELECT

Hi Frank, On Tue, May 26, 2020 at 9:23 AM Frank Millman wrote: > Hi all > > I have a SELECT that runs over 5 times slower on PostgreSQL compared > with Sql Server and sqlite3. I am trying to understand why. > > I have a table that looks like this (simplified) - > > CREATE TABLE my_table ( >

Slow SELECT

Hi all I have a SELECT that runs over 5 times slower on PostgreSQL compared with Sql Server and sqlite3. I am trying to understand why. I have a table that looks like this (simplified) - CREATE TABLE my_table ( row_id SERIAL PRIMARY KEY, deleted_id INT DEFAULT 0, fld_1 INT