Re: is there an immutable function to switch from date to character?

[Steve Baldwin]

On Thu, Apr 25, 2024 at 7:31 AM Celia McInnis 
wrote:

> create temporary table junk as select now()::date as evtdate;
>
> alter table junk add column chardate text GENERATED ALWAYS AS
> (to_char(evtdate,'-Mon-DD')) STORED;
>
> ERROR:  generation expression is not immutable
>
> Maybe this is a hack but..

b2bcreditonline=# create temporary table junk as select now()::date as
evtdate;
SELECT 1
b2bcreditonline=# alter table junk add column chardate text GENERATED
ALWAYS AS (to_char(evtdate,'-Mon-DD')) STORED;
ERROR:  generation expression is not immutable
b2bcreditonline=# create or replace function date_to_text(i_date in date)
returns text immutable language sql as $$ select to_char(i_date,
'-MM-DD') $$;
CREATE FUNCTION
b2bcreditonline=# alter table junk add column chardate text GENERATED
ALWAYS AS (date_to_text(evtdate)) STORED;
ALTER TABLE
b2bcreditonline=# select * from junk;
  evtdate   |  chardate
+
 2024-04-24 | 2024-04-24
(1 row)

Re: query multiple schemas

[Steve Baldwin]

Hi Norbi,

If the number of tenant schemas is reasonably static, you could write a
plpgsql function to create a set of UNION ALL views with one view for each
table in all tenant schemas. You could re-run the function each time a
tenant schema is added. Having the set of views would allow you to query
them as you would any of the underlying tables, and the query planner could
likely optimise the query better. With your current function, if you needed
to add a WHERE clause and the underlying tables were large, it would likely
not perform as well as the UNION ALL view.

Cheers,

Steve

On Mon, Apr 22, 2024 at 6:12 AM Norbert Sándor 
wrote:

> Hello,
>
> I have a database with multiple tenants with a separate schema for each
> tenant.
> The structure of each schema is identical, the tenant ID is the name of
> the schema.
>
> What I would like to achieve is to able to query tables in all schemas at
> once with the tenant IDs added to the result  set.
>
> I experimented with typed solutions like described in
> https://dev.to/staab/how-to-query-multiple-schemas-with-postgresql-28kj
> without much success.
> So I turned to a more dynamic JSON-based solution.
>
> Please note that I'm new to plpgsql, so *any* (even a less related)
> advice is welcome :)
>
> My current experimental function is:
>
> CREATE OR REPLACE FUNCTION demo.tenant_union_query(tbl anyelement)
>
> RETURNS setof json AS $func$
>
> declare
>
> _select text;
>
> begin
>
> _select := (select
>
> string_agg(
>
> format('select t.*, %L tenantId from %I.%I t', schema_name, schema_name,
> pg_typeof(tbl)),
>
> E'\n' || ' union all ' || E'\n')
>
> from (
>
> SELECT schema_name
>
> FROM information_schema.schemata
>
> where schema_name not in ('information_schema') and schema_name not like
> 'pg_%'
>
> ) tenants
>
> );
>
> return query execute 'select row_to_json(r) from (' || _select || ') as r'
> ;
>
> END;
>
> $func$ LANGUAGE plpgsql;
>
> And this is how I use it to query a "usual" result-set-like result with
> the tenant ID in the 1st column, followed by the fields from the given
> table:
>
> select r->>'tenantid' tenantId, json_populate_record(null::mytable, r)
> from tenant_union_query(null::mytable) r order by tenantId; -- (1)
>
> The above solution seems to work, my questions are:
>
>1. Is there a better way to achieve the same functionality? Maybe
>without using JSON as an intermediate representation?
>2. Is there a way to further simplify the usage, like instead of the
>query (1) above something more simple, like: select * from
>tenant_union_query_2(null::mytable) order by tenantId;
>
> Thanks for your help in advance.
> Best regards,
> Norbi
>

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

[Steve Baldwin]

Could you use an interval data type? For example:

b2bcreditonline=# create table interval_example (i interval);
CREATE TABLE
b2bcreditonline=# insert into interval_example values
('26:15:32'::interval);
INSERT 0 1
b2bcreditonline=# select * from interval_example;
i
--
 26:15:32
(1 row)

b2bcreditonline=# select i, i + interval '45 minutes' as plus from
interval_example;
i |   plus
--+--
 26:15:32 | 27:00:32
(1 row)

Steve

On Wed, Mar 20, 2024 at 1:05 PM Celia McInnis 
wrote:

> Hi:
>
> I want to store times in a database as hours:minutes:seconds where hours
> can be greater than 24. How do I do this? I will want to be able to add
> such times.
>
> Thanks,
> Celia McInnis
>

Re: Help diagnosing replication (copy) error

[Steve Baldwin]

On Sun, Mar 10, 2024 at 3:16 AM Adrian Klaver 
wrote:

>
> Is the case still active?
> Can you get information from them about what they saw?
>
> I've re-opened the case and asked for an explanation of the error and what
they did to resolve it.

Hopefully they shed some light on the 'mystery'.

Steve

Re: Help diagnosing replication (copy) error

[Steve Baldwin]

On Sat, Mar 9, 2024 at 11:06 AM Jeff Ross  wrote:

>
> RDS is a black box--who knows what's really going on there?  It would be
> interesting to see what the response is after you open a support case.
> I hope you'll be able to share that with the list.
>
> This is very mysterious. I logged the case, and before it had been picked
up by an analyst, the issue somehow resolved itself without me doing
anything.

I now have 418M+ rows in the table that it got stuck on.

:shrug:

Thanks Adrian and Jeff for responding.

Steve

> Jeff
>
>
>
>
>
>
>

Re: Help diagnosing replication (copy) error

[Steve Baldwin]

On Sat, Mar 9, 2024 at 9:13 AM Adrian Klaver 
wrote:

>
> I should been clearer.
>
> What are the CREATE PUBLICATION and CREATE SUBSCRIPTION statements?
>
> The publications were created a while ago. Does this help:

b2bcreditonline=> select * from pg_publication;
-[ RECORD 1 ]+-
oid  | 18829
pubname  | b2bc_master
pubowner | 16760
puballtables | f
pubinsert| t
pubupdate| t
pubdelete| t
pubtruncate  | t
pubviaroot   | f
-[ RECORD 2 ]+-
oid  | 18830
pubname  | b2bc_master_only
pubowner | 16760
puballtables | f
pubinsert| t
pubupdate| t
pubdelete| t
pubtruncate  | t
pubviaroot   | f
-[ RECORD 3 ]+-
oid  | 18831
pubname  | b2bc_shard
pubowner | 16760
puballtables | f
pubinsert| t
pubupdate| t
pubdelete| t
pubtruncate  | t
pubviaroot   | f

The publication getting 'stuck' is b2bc_shard. It defines a bunch of
tables, one of which is audit.log_row. This table is quite large (600+ GB).

b2bcreditonline=> select * from pg_publication_rel where prrelid =
'audit.log_row'::regclass;
-[ RECORD 1 ]--
oid | 18832
prpubid | 18831
prrelid | 60067
prqual  |
prattrs |

Here's the subscription info:

b2bcreditonline=> select * from pg_subscription;
-[ RECORD 1
]---+---
oid | 378075175
subdbid | 16404
subskiplsn  | 0/0
subname | b2bcreditonline_prod_e_master
subowner| 16388
subenabled  | t
subbinary   | f
substream   | f
subtwophasestate| d
subdisableonerr | f
subpasswordrequired | t
subrunasowner   | f
subconninfo | host=
b2bcreditonline-prod-d.cliwvveusxrd.us-east-1.rds.amazonaws.com user=xxx
password=xxx dbname=b2bcreditonline
subslotname | b2bcreditonline_prod_e_master
subsynccommit   | off
subpublications | {b2bc_master}
suborigin   | any
-[ RECORD 2
]---+---
oid | 378075176
subdbid | 16404
subskiplsn  | 0/0
subname | b2bcreditonline_prod_e_master_only
subowner| 16388
subenabled  | t
subbinary   | f
substream   | f
subtwophasestate| d
subdisableonerr | f
subpasswordrequired | t
subrunasowner   | f
subconninfo | host=
b2bcreditonline-prod-d.cliwvveusxrd.us-east-1.rds.amazonaws.com user=xxx
password=xxx dbname=b2bcreditonline
subslotname | b2bcreditonline_prod_e_master_only
subsynccommit   | off
subpublications | {b2bc_master_only}
suborigin   | any
-[ RECORD 3
]---+---
oid | 378075177
subdbid | 16404
subskiplsn  | 0/0
subname | b2bcreditonline_prod_e_shard
subowner| 16388
subenabled  | t
subbinary   | f
substream   | f
subtwophasestate| d
subdisableonerr | f
subpasswordrequired | t
subrunasowner   | f
subconninfo | host=
b2bcreditonline-prod-d.cliwvveusxrd.us-east-1.rds.amazonaws.com user=xxx
password=xxx dbname=b2bcreditonline
subslotname | b2bcreditonline_prod_e_shard
subsynccommit   | off
subpublications | {b2bc_shard}
suborigin   | any

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

Re: Help diagnosing replication (copy) error

[Steve Baldwin]

On Sat, Mar 9, 2024 at 8:56 AM Adrian Klaver 
wrote:

>
> What are the rest of the values in pg_replication_slots?
>
> b2bcreditonline=> select * from pg_replication_slots;
  slot_name  |  plugin  | slot_type |
datoid |database | temporary | active | active_pid | xmin |
catalog_xmin |  restart_lsn  | confirmed_flush_lsn | wal_status |
safe_wal_size | two_phase
-+--+---++-+---+++--+--+---+-++---+---
 b2bcreditonline_prod_e_master   | pgoutput | logical   |
 16404 | b2bcreditonline | f | t  |  13700 |  |
 456150233 | 2E2F/AC07B760 | 2E2F/AEEEA478   | reserved   |
  | f
 b2bcreditonline_prod_sandbox_d_master   | pgoutput | logical   |
 16404 | b2bcreditonline | f | t  |   9232 |  |
 456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560   | reserved   |
  | f
 b2bcreditonline_prod_e_master_only  | pgoutput | logical   |
 16404 | b2bcreditonline | f | t  |  13710 |  |
 456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560   | reserved   |
  | f
 pg_378075177_sync_60067_7343845372910323059 | pgoutput | logical   |
 16404 | b2bcreditonline | f | f  ||  |
 455719618 | 2E2A/1C0972E0 | 2E2A/1C097318   | extended   |
  | f
 b2bcreditonline_prod_e_shard| pgoutput | logical   |
 16404 | b2bcreditonline | f | t  |  13718 |  |
 456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560   | reserved   |
  | f
(5 rows)


> Is there data in the subscriber side table?
>
> No there is not, although when I did a 'select count(*) from {table}' it
took several minutes to return 0 rows.


> What are the publisher and subscriber configurations?
>
> Not sure which settings, but here's a few.

(publisher - pg 15.3)
b2bcreditonline=> select name, setting from pg_settings where name like
'max\_%' escape '\';
   name| setting
---+-
 max_connections   | 5000
 max_files_per_process | 1000
 max_function_args | 100
 max_identifier_length | 63
 max_index_keys| 32
 max_locks_per_transaction | 64
 max_logical_replication_workers   | 16
 max_parallel_maintenance_workers  | 2
 max_parallel_workers  | 8
 max_parallel_workers_per_gather   | 2
 max_pred_locks_per_page   | 2
 max_pred_locks_per_relation   | -2
 max_pred_locks_per_transaction| 64
 max_prepared_transactions | 0
 max_replication_slots | 64
 max_slot_wal_keep_size| -1
 max_stack_depth   | 6144
 max_standby_archive_delay | 3
 max_standby_streaming_delay   | 3
 max_sync_workers_per_subscription | 4
 max_wal_senders   | 96
 max_wal_size  | 4096
 max_worker_processes  | 32
(23 rows)

(subscriber - pg 16.2)
b2bcreditonline=> select name, setting from pg_settings where name like
'max\_%' escape '\';
name | setting
-+-
 max_connections | 5000
 max_files_per_process   | 1000
 max_function_args   | 100
 max_identifier_length   | 63
 max_index_keys  | 32
 max_locks_per_transaction   | 64
 max_logical_replication_workers | 16
 max_parallel_apply_workers_per_subscription | 2
 max_parallel_maintenance_workers| 2
 max_parallel_workers| 8
 max_parallel_workers_per_gather | 2
 max_pred_locks_per_page | 2
 max_pred_locks_per_relation | -2
 max_pred_locks_per_transaction  | 64
 max_prepared_transactions   | 0
 max_replication_slots   | 64
 max_slot_wal_keep_size  | -1
 max_stack_depth | 6144
 max_standby_archive_delay   | 3
 max_standby_streaming_delay | 3
 max_sync_workers_per_subscription   | 4
 max_wal_senders | 96
 max_wal_size| 4096
 max_worker_processes| 32
(24 rows)


>
> >
> > I've checked the recent logs for both the publishing cluster and the
> > subscribing cluster but I can't see any replication errors. I guess I
> > could have missed them, but it doesn't seem like anything is being
> > 'retried' like I've seen in the past with replication errors.
> >
> > I've used this mechanism for zero-downtime upgrades multiple times in
> > the past, and have recently used it to upgrade smaller clusters from
> > 15.x to 

Help diagnosing replication (copy) error

[Steve Baldwin]

Hi,

I'm in the process of migrating a cluster from 15.3 to 16.2. We have a
'zero downtime' requirement so I'm using logical replication to create the
new cluster and then perform the switch in the application.

I have a situation where all but one table have done their initial copy.
The remaining table is the largest (of course), and the replication slot
that is assigned for the copy (pg_378075177_sync_60067_7343845372910323059)
is showing as 'active=false' if I select from pg_replication_slots on the
publisher.

I've checked the recent logs for both the publishing cluster and the
subscribing cluster but I can't see any replication errors. I guess I could
have missed them, but it doesn't seem like anything is being 'retried' like
I've seen in the past with replication errors.

I've used this mechanism for zero-downtime upgrades multiple times in the
past, and have recently used it to upgrade smaller clusters from 15.x to
16.2 without issue.

The clusters are hosted on AWS RDS, so I have no access to the servers, but
if that's the only way to diagnose the issue, I can create a support case.

Does anyone have any suggestions as to where I should look for the issue?

Thanks,

Steve

Re: Can user specification of a column value be required when querying a view ?

[Steve Baldwin]

Maybe you could create a function that has a required parameter, so rather
than 'select * from huge_view where .." they do 'select * from
function(some_ssn) [where...]' ?

That function would then query the view using the supplied ssn.

Just a thought.

Steve

On Tue, Nov 21, 2023 at 8:41 AM David Gauthier 
wrote:

> Hi:
>
> I have a view that I want  to require user specification for a specific
> column before the query starts (if that makes sense).
>
> Example
> I want the users to be required to provide a value for ssn in the
> following query...
> "select * from huge_view *where ssn = '106-91-9930'* "
> I never want them to query the view without specifying ssn.
> It has to do with resources and practicality.
>
> Is there a way to do that ?
> Thanks
>
>

Re: psql trying twice to connect to local DB

[Steve Baldwin]

It seems this is not restricted to local connections. Here's the log
entries from making a connection with the same psql client to a remote
(RDS) instance:

2023-10-07 22:47:06.506
UTC,,,23005,"10.117.100.26:55302",6521dfea.59dd,1,"",2023-10-07
22:47:06 UTC,,0,LOG,0,"connection received: host=10.117.100.26
port=55302","","not initialized",,0
2023-10-07 22:47:08.479
UTC,,,23010,"10.117.100.26:55306",6521dfec.59e2,1,"",2023-10-07
22:47:08 UTC,,0,LOG,0,"connection received: host=10.117.100.26
port=55306","","not initialized",,0
2023-10-07 22:47:09.992 UTC,"b2bc_owner","b2bcreditonline",23010,"
10.117.100.26:55306",6521dfec.59e2,2,"authentication",2023-10-07 22:47:08
UTC,29/840773,0,LOG,0,"connection authenticated:
identity=""b2bc_owner"" method=md5
(/rdsdbdata/config/pg_hba.conf:14)","","client backend",,0
2023-10-07 22:47:09.993 UTC,"b2bc_owner","b2bcreditonline",23010,"
10.117.100.26:55306",6521dfec.59e2,3,"authentication",2023-10-07 22:47:08
UTC,29/840773,0,LOG,0,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=psql SSL enabled
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384,
bits=256)","","client backend",,0

Compared to a connection made from our application:

2023-10-07 22:28:26.565
UTC,,,22121,"10.120.166.33:47748",6521db8a.5669,1,"",2023-10-07
22:28:26 UTC,,0,LOG,0,"connection received: host=10.120.166.33
port=47748","","not initialized",,0
2023-10-07 22:28:26.583 UTC,"b2bc_api","b2bcreditonline",22121,"
10.120.166.33:47748",6521db8a.5669,2,"authentication",2023-10-07 22:28:26
UTC,18/1263698,0,LOG,0,"connection authenticated: identity=""b2bc_api""
method=md5 (/rdsdbdata/config/pg_hba.conf:14)","","client
backend",,0
2023-10-07 22:28:26.583 UTC,"b2bc_api","b2bcreditonline",22121,"
10.120.166.33:47748",6521db8a.5669,3,"authentication",2023-10-07 22:28:26
UTC,18/1263698,0,LOG,0,"connection authorized: user=b2bc_api
database=b2bcreditonline SSL enabled (protocol=TLSv1.2,
cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)","","client
backend",,0

It seems psql 16.0 (homebrew) behaves the same.

Steve

On Sun, Oct 8, 2023 at 9:44 AM Steve Baldwin 
wrote:

> I have a local DB (15.2) running in a docker container.
>
> If I make a connection to that DB from most clients I see log entries like
> this:
>
> 2023-10-07 22:32:26.518 
> UTC,,,16278,"172.21.0.1:33192",6521dc7a.3f96,1,"",2023-10-07
> 22:32:26 UTC,,0,LOG,0,"connection received: host=172.21.0.1
> port=33192","","not initialized",,0
> 2023-10-07 22:32:26.529 UTC,"b2bc_owner","b2bcreditonline",16278,"
> 172.21.0.1:33192",6521dc7a.3f96,2,"authentication",2023-10-07 22:32:26
> UTC,4/340,0,LOG,0,"connection authenticated: identity=""b2bc_owner""
> method=scram-sha-256
> (/var/lib/postgresql/data/pg_hba.conf:100)","","client backend",,0
> 2023-10-07 22:32:26.529 UTC,"b2bc_owner","b2bcreditonline",16278,"
> 172.21.0.1:33192",6521dc7a.3f96,3,"authentication",2023-10-07 22:32:26
> UTC,4/340,0,LOG,0,"connection authorized: user=b2bc_owner
> database=b2bcreditonline application_name=crystal","","client
> backend",,0
>
> If however I use psql I see two connection log entries - sometimes almost
> a second apart. For example:
>
> 2023-10-07 22:33:16.214 
> UTC,,,16431,"172.21.0.1:48550",6521dcac.402f,1,"",2023-10-07
> 22:33:16 UTC,,0,LOG,0,"connection received: host=172.21.0.1
> port=48550","","not initialized",,0
> 2023-10-07 22:33:17.130 
> UTC,,,16432,"172.21.0.1:48558",6521dcad.4030,1,"",2023-10-07
> 22:33:17 UTC,,0,LOG,0,"connection received: host=172.21.0.1
> port=48558","","not initialized",,0
> 2023-10-07 22:33:17.151 UTC,"b2bc_owner","b2bcreditonline",16432,"
> 172.21.0.1:48558",6521dcad.4030,2,"authentication",2023-10-07 22:33:17
> UTC,4/354,0,LOG,0,"connection authenticated: identity=""b2bc_owner""
> method=scram-sha-256
> (/var/lib/postgresql/data/pg_hba.conf:100)","","client backend",,0
> 2023-10-07 22:33:17.152 UTC,"b2bc_owner","b2bcreditonline",16432,"
> 172.21.0.1:48558",6521dcad.4030,3,"authentication",2023-10-07 22:33:17
> UTC,4/354,0,LOG,0,"connection authorized: user=b2bc_owner
> database=b2bcreditonline application_name=psql","","client
> backend",,0
>
> The version of psql is 15.4.
>
> Obviously this isn't a major problem, but I'm curious if I've got
> something configured incorrectly, or just something I missed from the docs.
>
> Thanks,
>
> Steve
>

psql trying twice to connect to local DB

[Steve Baldwin]

I have a local DB (15.2) running in a docker container.

If I make a connection to that DB from most clients I see log entries like
this:

2023-10-07 22:32:26.518
UTC,,,16278,"172.21.0.1:33192",6521dc7a.3f96,1,"",2023-10-07
22:32:26 UTC,,0,LOG,0,"connection received: host=172.21.0.1
port=33192","","not initialized",,0
2023-10-07 22:32:26.529 UTC,"b2bc_owner","b2bcreditonline",16278,"
172.21.0.1:33192",6521dc7a.3f96,2,"authentication",2023-10-07 22:32:26
UTC,4/340,0,LOG,0,"connection authenticated: identity=""b2bc_owner""
method=scram-sha-256
(/var/lib/postgresql/data/pg_hba.conf:100)","","client backend",,0
2023-10-07 22:32:26.529 UTC,"b2bc_owner","b2bcreditonline",16278,"
172.21.0.1:33192",6521dc7a.3f96,3,"authentication",2023-10-07 22:32:26
UTC,4/340,0,LOG,0,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=crystal","","client
backend",,0

If however I use psql I see two connection log entries - sometimes almost a
second apart. For example:

2023-10-07 22:33:16.214
UTC,,,16431,"172.21.0.1:48550",6521dcac.402f,1,"",2023-10-07
22:33:16 UTC,,0,LOG,0,"connection received: host=172.21.0.1
port=48550","","not initialized",,0
2023-10-07 22:33:17.130
UTC,,,16432,"172.21.0.1:48558",6521dcad.4030,1,"",2023-10-07
22:33:17 UTC,,0,LOG,0,"connection received: host=172.21.0.1
port=48558","","not initialized",,0
2023-10-07 22:33:17.151 UTC,"b2bc_owner","b2bcreditonline",16432,"
172.21.0.1:48558",6521dcad.4030,2,"authentication",2023-10-07 22:33:17
UTC,4/354,0,LOG,0,"connection authenticated: identity=""b2bc_owner""
method=scram-sha-256
(/var/lib/postgresql/data/pg_hba.conf:100)","","client backend",,0
2023-10-07 22:33:17.152 UTC,"b2bc_owner","b2bcreditonline",16432,"
172.21.0.1:48558",6521dcad.4030,3,"authentication",2023-10-07 22:33:17
UTC,4/354,0,LOG,0,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=psql","","client
backend",,0

The version of psql is 15.4.

Obviously this isn't a major problem, but I'm curious if I've got something
configured incorrectly, or just something I missed from the docs.

Thanks,

Steve

Re: regex failing

[Steve Baldwin]

Probably not much help but it seems to work ok for me (unless I'm doing
something stupid). You didn't actually say how/where you were doing the
regex match, but the '~' operator seems to work ok.

[~/git/caas-sqs-consumer] psql
psql (15.3 (Homebrew), server 13.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
compression: off)
Type "help" for help.

bcaas=> select '1234:56' ~ '^([0-9]+[.]?[0-9]*)$';
 ?column?
--
 f
(1 row)

bcaas=> select '1234.56' ~ '^([0-9]+[.]?[0-9]*)$';
 ?column?
--
 t
(1 row)

On Wed, Jun 28, 2023 at 4:59 AM Zahir Lalani 
wrote:

> Hi All
>
>
>
> Got a weird one. I am using the regex below to case match numeric only
> values.
>
>
>
> '^([0-9]+[.]?[0-9]*)$'
>
>
>
> This works well by and large but not for a value like “1234:567”. This
> seems to match positive and then fails due to not being an actual number in
> the subsequent cast.
>
>
>
> Any ideas? (PG13)
>
>
>
> Z
>
>
>

Re: Connection error to new pg15 instance

[Steve Baldwin]

It turns out RDS by default forces ssl connections for pg15 instances. I
have turned this off for now while I work out how that impacts my code and
I can now connect to the pg15 instance.

Thanks,

Steve

On Sun, Jun 4, 2023 at 3:10 PM Steve Baldwin 
wrote:

> I suspect it may have something to do with ssl. The FATAL error in the log
> said "no encryption". I'm not sure what that means. When I look at a
> connection to one of our pg14 instances from the same API server, I see
> this in the logs:
>
> 2023-06-04 00:03:06.210 UTC,"b2bc_api","b2bcreditonline",16024,"
> 10.120.141.112:49228",647bd4ba.3e98,2,"authentication",2023-06-04
> 00:03:06 UTC,25/2682741,0,LOG,0,"connection authenticated:
> identity=""b2bc_api"" method=md5
> (/rdsdbdata/config/pg_hba.conf:13)","","client backend",,0
>
> Here are the hba rules for that instance:
>
> b2bcreditonline=> select * from pg_hba_file_rules;
>  line_number | type  | database  | user_name  | address  | netmask
> |  auth_method  | options | error
>
> -+---+---++--+-+---+-+---
>4 | local | {all} | {all}  |  |
> | scram-sha-256 | |
>   10 | host  | {all} | {rdsadmin} | samehost |
> | scram-sha-256 | |
>   11 | host  | {all} | {rdsadmin} | all  |
> | reject| |
>   12 | host  | {rdsadmin}| {all}  | all  |
> | reject| |
>   13 | host  | {all} | {all}  | all  |
> | md5   | |
>   14 | host  | {replication} | {all}  | samehost |
> | scram-sha-256 | |
>   17 | host  | {rds_replication} | {all}  | all  |
> | md5   | |
> (7 rows)
>
> So line 13 is type 'host'.
>
> When I look at the hba rules for the pg15 instance, there is no
> corresponding entry:
>
> b2bcreditonline=> select * from pg_hba_file_rules;
>  line_number |  type   | database  | user_name  | address  |
> netmask |  auth_method  |  options  | error
>
> -+-+---++--+-+---+---+---
>2 | local   | {all} | {rdsadmin} |  |
>   | peer  | {map=rds} |
>6 | local   | {all} | {all}  |  |
>   | scram-sha-256 |   |
>   12 | host| {all} | {rdsadmin} | samehost |
>   | scram-sha-256 |   |
>   13 | host| {all} | {rdsadmin} | all  |
>   | reject|   |
>   14 | host| {rdsadmin}| {all}  | all  |
>   | reject|   |
>   15 | hostssl | {all} | {all}  | all  |
>   | md5   |   |
>   16 | host| {replication} | {all}  | samehost |
>   | scram-sha-256 |   |
>   21 | hostssl | {rds_replication} | {all}  | all  |
>   | md5   |   |
> (8 rows)
>
> The entry that was used when I made a psql connection was line 15 which
> has a type of 'hostssl'.
>
> I'm not sure what this means in terms of what I need to change. Maybe I
> need to raise this with AWS support?
>
> Cheers,
>
> Steve
>
> On Sun, Jun 4, 2023 at 11:11 AM Steve Baldwin 
> wrote:
>
>> Hi all,
>>
>> I'm in the process of migrating from an RDS pg14 instance to pg15.3. As
>> part of the migration process, the application code makes a test connection
>> to the new instance. This failed. I tried manually connecting to the kube
>> pod where the test query was submitted from, and from there was able to
>> manually connect to the new instance (using psql) just fine.
>>
>> Here are the (hopefully) relevant chunks from the database log:
>>
>> :
>> 2023-06-04 00:29:11.890 
>> UTC,,,2764,"10.120.80.80:46914",647bdad7.acc,1,"",2023-06-04
>> 00:29:11 UTC,,0,LOG,0,"connection received: host=10.120.80.80
>> port=46914","","not initialized",,0
>> 2023-06-04 00:29:11.891 UTC,"b2bc_owner","b2bcreditonline",2764,"
>> 10.120.80.80:46914",647bdad7.acc,2,"authentication",2023-06-04 00:29:11
>> UTC,7/1009,0,FATAL,28000,"no pg_hba.conf entry for host ""10.120.80.80"",
>> user ""b2bc_owner"", database ""b2bcreditonline"", no
>> encryption",

Re: Connection error to new pg15 instance

[Steve Baldwin]

I suspect it may have something to do with ssl. The FATAL error in the log
said "no encryption". I'm not sure what that means. When I look at a
connection to one of our pg14 instances from the same API server, I see
this in the logs:

2023-06-04 00:03:06.210 UTC,"b2bc_api","b2bcreditonline",16024,"
10.120.141.112:49228",647bd4ba.3e98,2,"authentication",2023-06-04 00:03:06
UTC,25/2682741,0,LOG,0,"connection authenticated: identity=""b2bc_api""
method=md5 (/rdsdbdata/config/pg_hba.conf:13)","","client
backend",,0

Here are the hba rules for that instance:

b2bcreditonline=> select * from pg_hba_file_rules;
 line_number | type  | database  | user_name  | address  | netmask
|  auth_method  | options | error
-+---+---++--+-+---+-+---
   4 | local | {all} | {all}  |  |
| scram-sha-256 | |
  10 | host  | {all} | {rdsadmin} | samehost |
| scram-sha-256 | |
  11 | host  | {all} | {rdsadmin} | all  |
| reject| |
  12 | host  | {rdsadmin}| {all}  | all  |
| reject| |
  13 | host  | {all} | {all}  | all  |
| md5   | |
  14 | host  | {replication} | {all}  | samehost |
| scram-sha-256 | |
  17 | host  | {rds_replication} | {all}  | all  |
| md5   | |
(7 rows)

So line 13 is type 'host'.

When I look at the hba rules for the pg15 instance, there is no
corresponding entry:

b2bcreditonline=> select * from pg_hba_file_rules;
 line_number |  type   | database  | user_name  | address  |
netmask |  auth_method  |  options  | error
-+-+---++--+-+---+---+---
   2 | local   | {all} | {rdsadmin} |  |
  | peer  | {map=rds} |
   6 | local   | {all} | {all}  |  |
  | scram-sha-256 |   |
  12 | host| {all} | {rdsadmin} | samehost |
  | scram-sha-256 |   |
  13 | host| {all} | {rdsadmin} | all  |
  | reject|   |
  14 | host| {rdsadmin}| {all}  | all  |
  | reject|   |
  15 | hostssl | {all} | {all}  | all  |
  | md5   |   |
  16 | host| {replication} | {all}  | samehost |
  | scram-sha-256 |   |
  21 | hostssl | {rds_replication} | {all}  | all  |
  | md5   |   |
(8 rows)

The entry that was used when I made a psql connection was line 15 which has
a type of 'hostssl'.

I'm not sure what this means in terms of what I need to change. Maybe I
need to raise this with AWS support?

Cheers,

Steve

On Sun, Jun 4, 2023 at 11:11 AM Steve Baldwin 
wrote:

> Hi all,
>
> I'm in the process of migrating from an RDS pg14 instance to pg15.3. As
> part of the migration process, the application code makes a test connection
> to the new instance. This failed. I tried manually connecting to the kube
> pod where the test query was submitted from, and from there was able to
> manually connect to the new instance (using psql) just fine.
>
> Here are the (hopefully) relevant chunks from the database log:
>
> :
> 2023-06-04 00:29:11.890 
> UTC,,,2764,"10.120.80.80:46914",647bdad7.acc,1,"",2023-06-04
> 00:29:11 UTC,,0,LOG,0,"connection received: host=10.120.80.80
> port=46914","","not initialized",,0
> 2023-06-04 00:29:11.891 UTC,"b2bc_owner","b2bcreditonline",2764,"
> 10.120.80.80:46914",647bdad7.acc,2,"authentication",2023-06-04 00:29:11
> UTC,7/1009,0,FATAL,28000,"no pg_hba.conf entry for host ""10.120.80.80"",
> user ""b2bc_owner"", database ""b2bcreditonline"", no
> encryption","","client backend",,0
> :
> 2023-06-04 00:43:56.114 
> UTC,,,4046,"10.120.80.80:56356",647bde4c.fce,1,"",2023-06-04
> 00:43:56 UTC,,0,LOG,0,"connection received: host=10.120.80.80
> port=56356","","not initialized",,0
> 2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
> 10.120.80.80:56356",647bde4c.fce,2,"authentication",2023-06-04 00:43:56
> UTC,7/1626,0,LOG,0,"connection authenticated: identity=""b2bc_owner""
> method=md5 (/rdsdbdata/config/pg_hba.conf:15)","","client
> backend&q

Connection error to new pg15 instance

[Steve Baldwin]

Hi all,

I'm in the process of migrating from an RDS pg14 instance to pg15.3. As
part of the migration process, the application code makes a test connection
to the new instance. This failed. I tried manually connecting to the kube
pod where the test query was submitted from, and from there was able to
manually connect to the new instance (using psql) just fine.

Here are the (hopefully) relevant chunks from the database log:

:
2023-06-04 00:29:11.890
UTC,,,2764,"10.120.80.80:46914",647bdad7.acc,1,"",2023-06-04
00:29:11 UTC,,0,LOG,0,"connection received: host=10.120.80.80
port=46914","","not initialized",,0
2023-06-04 00:29:11.891 UTC,"b2bc_owner","b2bcreditonline",2764,"
10.120.80.80:46914",647bdad7.acc,2,"authentication",2023-06-04 00:29:11
UTC,7/1009,0,FATAL,28000,"no pg_hba.conf entry for host ""10.120.80.80"",
user ""b2bc_owner"", database ""b2bcreditonline"", no
encryption","","client backend",,0
:
2023-06-04 00:43:56.114
UTC,,,4046,"10.120.80.80:56356",647bde4c.fce,1,"",2023-06-04
00:43:56 UTC,,0,LOG,0,"connection received: host=10.120.80.80
port=56356","","not initialized",,0
2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,2,"authentication",2023-06-04 00:43:56
UTC,7/1626,0,LOG,0,"connection authenticated: identity=""b2bc_owner""
method=md5 (/rdsdbdata/config/pg_hba.conf:15)","","client
backend",,0
2023-06-04 00:43:56.127 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,3,"authentication",2023-06-04 00:43:56
UTC,7/1626,0,LOG,0,"connection authorized: user=b2bc_owner
database=b2bcreditonline application_name=psql SSL enabled
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384,
bits=256)","","client backend",,0
2023-06-04 00:43:58.814 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,4,"idle",2023-06-04 00:43:56
UTC,7/1627,0,LOG,0,"statement: select 0 as
dummy;","psql","client backend",,0
2023-06-04 00:43:58.814 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,5,"SELECT",2023-06-04 00:43:56
UTC,7/0,0,LOG,0,"duration: 0.341 ms","psql","client
backend",,1147616880456321454
2023-06-04 00:44:04.402 UTC,"b2bc_owner","b2bcreditonline",4046,"
10.120.80.80:56356",647bde4c.fce,6,"idle",2023-06-04 00:43:56
UTC,,0,LOG,0,"disconnection: session time: 0:00:08.287 user=b2bc_owner
database=b2bcreditonline host=10.120.80.80
port=56356","psql","client backend",,0
:

In case it's relevant, the application code is running on nodejs v18.14.2
using pg 8.11.0.

Any thoughts?

Thanks,

Steve

Re: Puzzled by ROW constructor behaviour?

[Steve Baldwin]

Hi Eagna,

Did you check the syntax of the INSERT statement? You either need 'VALUES
...' or a query. I don't think your expression on its own is considered a
query.

Cheers,

Steve

On Wed, Nov 23, 2022 at 8:11 AM Eagna  wrote:

>
> Hi all,
>
> I'm puzzled by some behaviour of the ROW constructor that I noticed when I
> was playing around.
>
> From the documentation (
> https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS),
> we have
>
> NUMBER 1
>
> > SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same') AS test1;
>
> result:
>
> > test1
> > f
>
> This is fine.
>
> and then
>
> NUMBER 2
>
> > SELECT ROW(1, 2.5, 'this is a test') = (VALUES (1, 2.5, 'this is a
> test')) AS test2;
>
> result:
>
> > test2
> > t
>
> OK - notice the equivalence of a ROW constructor and a VALUES clause.
>
> So, then I create this table:
>
> > CREATE TABLE test
> > (
> >  a INT NOT NULL,
> >  b INT NOT NULL,
> >  c TEXT NOT NULL
> > );
>
> and then tried:
>
> NUMBER 3
>
> > INSERT INTO test ((ROW (1, 2.4, 'test...')));
>
> and I get:
>
> > ERROR:  syntax error at or near "ROW"
> > LINE 1: INSERT INTO test ((ROW (1, 2.4, 'test...')));
>
>
> I tried various permutations of brackets and whatnot but nothing doing.
>
>
> My question is that if a ROW constructor works for a VALUES clause in
> statement NUMBER 2, then why not NUMBER 3?
>
>
> TIA and rgs,
>
> E.
>
>
>
>
>

Re: postgresql 15 for RHEL RPMs available?

[Steve Baldwin]

Looks like it's official ? - https://www.postgresql.org/docs/release/15.0/

On Mon, Oct 3, 2022 at 7:50 AM Michael Nolan  wrote:

> I was doing a check on updates available on my Centos 8 server and dnf is
> telling me that Postgresql 15 is available.
>
> I thought it was only at the RC1 state, but here's what I get:
>
> PostgreSQL 15 for RHEL / Rocky 8 - x86_6417  B/s | 195  B
> 00:11
> PostgreSQL 15 for RHEL / Rocky 8 - x86_64   1.6 MB/s | 1.7 kB
> 00:00
> Importing GPG key 0x442DF0F8:
>  Userid : "PostgreSQL RPM Building Project <
> pgsql-pkg-...@postgresql.org>"
>  Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
>  From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
>
> It doesn't indicate this as being RC1, is this in error?
> --
> Mike Nolan
> htf...@gmail.com
>

Re: problem with on conflict / do update using psql 14.4

[Steve Baldwin]

You need to prefix the rhs column(s) with 'excluded.'. For example:

on conflict (company_name) do update set company_name =
concat(excluded.company_name,'++',excluded.string_company_id);

On Sat, Sep 24, 2022 at 7:28 AM Barry Kimelman  wrote:

>
> I have not been able to get the "ON CONFLICT" clause to work
> I am using psql 14.4 on ubuntu
>
> Given the following table definition
>
> CREATE TABLE my_companies (
>   company_id SERIAL NOT NULL PRIMARY KEY,
>   second_id INTEGER NOT NULL REFERENCES big_list(second_id),
>   string_company_id TEXT NOT NULL,
>   company_name TEXT NOT NULL,
>   person_name TEXT NOT NULL
> )
>   INHERITS ( _audit_table, _delete_table );
>
> and the following constraints
>
> CREATE UNIQUE INDEX my_companies_id_unique ON
> my_companies(string_company_id) WHERE delete_timestamp IS NULL;
> CREATE UNIQUE INDEX my_companies_company_name_unique ON
> my_companies(company_name) WHERE delete_timestamp IS NULL;
>
> I issued the following sql insert in an attempt to use "on conflict" to
> update the
> duplicate column name
>
> insert into my_companies
> (second_id,string_company_id,company_name,person_name)
> values (1,'66','widgets','seller-toto')
> on conflict (company_name) do update set company_name =
> concat(company_name,'++',string_company_id);
>
> In this example a record with a company name of 'widgets' already exists
> and will
> result in an constraint violation
>
> when I ran my sql statement I received the following error message
>
> bkimelman=# \i insert-companies-1c.sql
> psql:insert-companies-1c.sql:3: ERROR:  column reference "company_name" is
> ambiguous
> LINE 3: ...company_name) do update set company_name = concat(company_na...
>
> I tried fully qualifying the column names in the concat() function call,
> but all that did was get me a different error message
>
> What would be the proper format for the "on conflict" clause ?
>

Re: Logging the query executed on the server

[Steve Baldwin]

On Sun, Jul 24, 2022 at 4:29 PM Igor Korot  wrote:

>
> 2 things:
> 1. How do I turn this off? ;-)
>

When you change the setting via 'set', that change is only for the current
session. You can revert it with 'set {some param} to default;' or just
terminate the session. If you want to make the change permanent, you need
to set it in the config file.

2. The log does show the query but it shows it with the placeholders.
> Is there a way to see the actual query?
>

Not sure what you mean here. The query shown in the log should be what is
actually executed by the server. If you are using placeholders, there are
probably 3 different log entries - one for the parse step, one for the bind
step and one for the execute step. If you are asking what are the bind
variable values, they are shown in the bind step. For example:

2022-07-24 07:00:00
UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:LOG:
duration: 0.072 ms bind :
select public.currency_on_mismatch() as on_mismatch,
set_config('search_path', $1, true),
set_config('application.user_id', $2, true),
set_config('application.app_client', $3, true),
set_config('application.api_client_id', $4 , true),
set_config('application.source', $5 , true),
set_config('application.request_id', $6 , true),
set_config('application.in_test_context', $7, true),
set_config('lock_timeout', $8, true),
txid_current()
2022-07-24 07:00:00
UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:DETAIL:
parameters: $1 = 'public', $2 = 'Admin/Support', $3 = 'Admin/Support', $4 =
'ce34e2bc-2c65-4fc1-9b95-878aef19a348', $5 = '?', $6 =
'4432dbb2-ab1c-4bd8-a413-ff5c704209a6', $7 = 'f', $8 = '10s'

If this doesn't help, maybe post what you're seeing in the log.

Cheers,

Steve

Re: Logging the query executed on the server

[Steve Baldwin]

Hi Igor,

Before you issue your query, try something like this:

(from psql, but hopefully you get the idea)

b2bcreditonline=# set log_min_duration_statement to 0;
SET
b2bcreditonline=# set log_statement to 'all';
SET

Ref: https://www.postgresql.org/docs/current/sql-set.html,
https://www.postgresql.org/docs/current/config-setting.html

Then submit your query and it will be set to the server log. You can get
the name of the current logfile with:

b2bcreditonline=# select pg_current_logfile();
 pg_current_logfile

 /log/pg.csv

HTH,

Steve

On Sun, Jul 24, 2022 at 3:26 PM Igor Korot  wrote:

> Hi,
> Is it possible to log the query that will be executed
> on the server?
>
> I'm writing an application that connects to the server
> through ODBC and libpq.
> For some reason ODBC interface is failing - it desn't
> return any rows
>
> So I'm thinking if I have a proof that the query I am
> actually executing is the same as the one I run through
> the psql - I will know where to look.
>
> I am actually binding some parameters and trying to
> execute the query.
>
> Thank you.
>
>
>

Re: Logical replication 'possible' problem

[Steve Baldwin]

The logical replication dump of the table I thought was 'stuck' eventually
completed after 6+ hours. I guess the replication slot showing active as
false is to be expected. I never noticed it before.

So there never was an issue - apart from my ignorance. Sorry for the noise.

Cheers,

Steve

On Wed, May 4, 2022 at 1:54 PM Steve Baldwin 
wrote:

> Sorry, I should have added the publisher is on 13.1 and the subscriber
> 14.2. Both are AWS RDS instances. I checked the log files for the publisher
> and subscriber and couldn't see any logical replication errors. The
> publisher is a busy DB though so if there are any errors there, I may have
> missed them.
>
> Thanks.
>
> On Wed, May 4, 2022 at 1:50 PM Steve Baldwin 
> wrote:
>
>> Hi,
>>
>> I'm in the process of doing the initial syncing of a subscriber with a
>> publisher.
>>
>> There is only one table that is still in a 'dumping' state. It is quite a
>> large table and in previous executions it took several hours.
>>
>> I'm not sure if it encountered a problem and stopped or if it is still
>> going.
>>
>> Looking at the replication slots on the publisher I see this:
>>
>> b2bcreditonline=> select slot_name, active, active_pid from
>> pg_replication_slots;
>>  slot_name  | active | active_pid
>> ++
>>  b2bcreditonline_prod_b_master  | t  |  21511
>>  b2bcreditonline_prod_b_shard   | t  |  21703
>>  pg_67491625_sync_60067_7093664237039303581 | f  |
>> (3 rows)
>>
>> I assume the pg_ slot is the one created for the initial copy but I'm
>> not sure if having a false active state is normal/ok.
>>
>> If it is, great. If not, how do I determine the problem and go about
>> fixing it?
>>
>> Thanks,
>>
>> Steve
>>
>

Re: Logical replication 'possible' problem

[Steve Baldwin]

Sorry, I should have added the publisher is on 13.1 and the subscriber
14.2. Both are AWS RDS instances. I checked the log files for the publisher
and subscriber and couldn't see any logical replication errors. The
publisher is a busy DB though so if there are any errors there, I may have
missed them.

Thanks.

On Wed, May 4, 2022 at 1:50 PM Steve Baldwin 
wrote:

> Hi,
>
> I'm in the process of doing the initial syncing of a subscriber with a
> publisher.
>
> There is only one table that is still in a 'dumping' state. It is quite a
> large table and in previous executions it took several hours.
>
> I'm not sure if it encountered a problem and stopped or if it is still
> going.
>
> Looking at the replication slots on the publisher I see this:
>
> b2bcreditonline=> select slot_name, active, active_pid from
> pg_replication_slots;
>  slot_name  | active | active_pid
> ++
>  b2bcreditonline_prod_b_master  | t  |  21511
>  b2bcreditonline_prod_b_shard   | t  |  21703
>  pg_67491625_sync_60067_7093664237039303581 | f  |
> (3 rows)
>
> I assume the pg_ slot is the one created for the initial copy but I'm
> not sure if having a false active state is normal/ok.
>
> If it is, great. If not, how do I determine the problem and go about
> fixing it?
>
> Thanks,
>
> Steve
>

Logical replication 'possible' problem

[Steve Baldwin]

Hi,

I'm in the process of doing the initial syncing of a subscriber with a
publisher.

There is only one table that is still in a 'dumping' state. It is quite a
large table and in previous executions it took several hours.

I'm not sure if it encountered a problem and stopped or if it is still
going.

Looking at the replication slots on the publisher I see this:

b2bcreditonline=> select slot_name, active, active_pid from
pg_replication_slots;
 slot_name  | active | active_pid
++
 b2bcreditonline_prod_b_master  | t  |  21511
 b2bcreditonline_prod_b_shard   | t  |  21703
 pg_67491625_sync_60067_7093664237039303581 | f  |
(3 rows)

I assume the pg_ slot is the one created for the initial copy but I'm
not sure if having a false active state is normal/ok.

If it is, great. If not, how do I determine the problem and go about fixing
it?

Thanks,

Steve

Re: Is this a reasonable use for advisory locks?

[Steve Baldwin]

Ok, so you want to allow _other_ updates to a customer while this process
is happening? In that case, advisory locks will probably work. The only
consideration is that the 'id' is a bigint. If your customer id maps to
that, great. If not (for example we use UUID's), you will need some way to
convert that id to a bigint.

Cheers,

Steve

On Thu, Apr 14, 2022 at 7:06 PM Perryn Fowler  wrote:

> Hi Steve,
>
> Thanks for your thoughts!
>
> I was thinking to avoid using locks on the customer rows because there is
> a lot of other unrelated access to that table. In particular I don’t want
> writes to that table queueing up behind this process.
>
> However, does the fact that you are suggesting  row locks mean you think
> advisory locks are a unsuitable?
>
> (Thanks for the mention of network issues, but I am confident that we have
> appropriate mechanisms in place to ensure fault tolerant and idempotent
> processing - I’m specifically wanting to address  the race condition)
>
> Cheers
> Perryn
>
> On Thu, 14 Apr 2022 at 6:38 pm, Steve Baldwin 
> wrote:
>
>> Hi Perryn,
>>
>> I don't know why you think advisory locks are the solution. It seems
>> regular row locks would ensure you have exclusive access to the customer.
>>
>> Maybe something like this:
>>
>> begin;
>> select * from customer where id = $1 for update skip locked;
>> if the query returns no rows it means something else already has a lock
>> on the customer so rollback and exit
>> otherwise call the external api (assume synchronous)
>> if successful insert a row into the ledger table and commit else rollback
>>
>> There are some tricky aspects to this but nothing that can be helped by
>> advisory locks over row locks. For example, if the external call takes too
>> long and you time out, or your network connection drops, how do you know
>> whether or not it was successful? You also need to work out what happens if
>> the insert into the ledger fails. If you haven't already, maybe check out
>> the 'saga' pattern.
>>
>> Cheers,
>>
>> Steve
>>
>> On Thu, Apr 14, 2022 at 5:11 PM Perryn Fowler  wrote:
>>
>>> Hi there,
>>>
>>> We have identified a problem that we think advisory locks could help
>>> with, but we wanted to get some advice on whether its a good idea to use
>>> them this way (and any tips, best practices or gotchas we should know about)
>>>
>>> THE PROBLEM
>>>
>>> We have some code that does the following
>>>- For a customer:
>>>  - sum a ledger of transactions
>>>  - if the result shows that money is owed:
>>> - charge a credit card (via a call to an external
>>> api)
>>> - if the charge is successful, insert a
>>> transaction into the ledger
>>>
>>> We would like to serialise execution of this code on a per customer
>>> basis, so that
>>> we do not double charge their credit card if execution happens
>>> concurrently.
>>>
>>> We are considering taking an advisory lock using the customer id to
>>> accomplish this.
>>>
>>> OUR CONCERNS
>>>   - The fact that the key for an advisory lock is an integer makes
>>> us wonder if this is designed for taking locks per process type, rather
>>> than per record (like a customer)
>>>   - Is it a bad idea to hold an advisory lock while an external api
>>> call happens? Should the locks be shorter lived?
>>>   - The documentation notes that these locks live in a memory pool
>>> and that 'care should be taken not to exhaust this memory'. What are the
>>> implications if it is exhausted? (Eg will the situation recover once locks
>>> are released?). Are there established patterns for detecting and preventing
>>> this situation?
>>>   - anything else we should know?
>>>
>>>
>>> Thanks in advance for any advice!
>>>
>>> Cheers
>>> Perryn
>>>
>>

Re: Is this a reasonable use for advisory locks?

[Steve Baldwin]

Hi Perryn,

I don't know why you think advisory locks are the solution. It seems
regular row locks would ensure you have exclusive access to the customer.

Maybe something like this:

begin;
select * from customer where id = $1 for update skip locked;
if the query returns no rows it means something else already has a lock on
the customer so rollback and exit
otherwise call the external api (assume synchronous)
if successful insert a row into the ledger table and commit else rollback

There are some tricky aspects to this but nothing that can be helped by
advisory locks over row locks. For example, if the external call takes too
long and you time out, or your network connection drops, how do you know
whether or not it was successful? You also need to work out what happens if
the insert into the ledger fails. If you haven't already, maybe check out
the 'saga' pattern.

Cheers,

Steve

On Thu, Apr 14, 2022 at 5:11 PM Perryn Fowler  wrote:

> Hi there,
>
> We have identified a problem that we think advisory locks could help with,
> but we wanted to get some advice on whether its a good idea to use them
> this way (and any tips, best practices or gotchas we should know about)
>
> THE PROBLEM
>
> We have some code that does the following
>- For a customer:
>  - sum a ledger of transactions
>  - if the result shows that money is owed:
> - charge a credit card (via a call to an external api)
> - if the charge is successful, insert a
> transaction into the ledger
>
> We would like to serialise execution of this code on a per customer basis,
> so that
> we do not double charge their credit card if execution happens
> concurrently.
>
> We are considering taking an advisory lock using the customer id to
> accomplish this.
>
> OUR CONCERNS
>   - The fact that the key for an advisory lock is an integer makes us
> wonder if this is designed for taking locks per process type, rather than
> per record (like a customer)
>   - Is it a bad idea to hold an advisory lock while an external api
> call happens? Should the locks be shorter lived?
>   - The documentation notes that these locks live in a memory pool and
> that 'care should be taken not to exhaust this memory'. What are the
> implications if it is exhausted? (Eg will the situation recover once locks
> are released?). Are there established patterns for detecting and preventing
> this situation?
>   - anything else we should know?
>
>
> Thanks in advance for any advice!
>
> Cheers
> Perryn
>

Re: Subscription stuck at initialize state

[Steve Baldwin]

Hi Abishek,

Have you checked the subscriber and publisher database log files to see if
there is a problem blocking the subscription? For example, a subscribed
table missing a column that exists in the publisher.

Cheers,

Steve

On Wed, Feb 2, 2022 at 1:26 PM Abhishek Bhola <
abhishek.bh...@japannext.co.jp> wrote:

> I have 2 sets of publication/subscription between my 2 DBs.
> One of them is working fine and the other one is stuck at initializing
> state for all the tables.
>
> sourcedb=# select * from pg_publication;
>   oid  |pubname | pubowner | puballtables | pubinsert |
> pubupdate | pubdelete | pubtruncate | pubviaroot
>
> ---++--+--+---+---+---+-+
>  19585 | omx_archive_big_tables |16420 | f| t | t
> | t | t   | f
>  19584 | omx_archive|16420 | f| t
> | t | t | t   | f
>
>
> targetdb=# select * from pg_subscription_rel ;
>   srsubid   |  srrelid   | srsubstate |   srsublsn
> +++---
>  3615804367 | 3322052690 | i  |
>  3615804367 | 3322052570 | i  |
>  3615756798 | 3322051793 | r  | 9E7E/BF5F82D8
>  3615804367 | 3322052133 | i  |
>  3615804367 | 3322054214 | i  |
>  3615756798 | 3322051802 | r  | 9E7E/C149BBD8
>  3615804367 | 3322051757 | i  |
>
> targetdb=# select * from pg_subscription;
> -[ RECORD 1
> ]---+--
> oid | 3615756798
> subdbid | 16589
> subname | sub_omx_archive_big_tables_tci
> subowner| 16420
> subenabled  | t
> subconninfo | xxx
> subslotname | sub_omx_archive_big_tables_tci
> subsynccommit   | off
> subpublications | {omx_archive_big_tables}
> -[ RECORD 2
> ]---+--
> oid | 3615804367
> subdbid | 16589
> subname | sub_omx_archive_tci
> subowner| 16420
> subenabled  | t
> subconninfo | 
> subslotname | sub_omx_archive_tci
> subsynccommit   | off
> subpublications | {omx_archive}
>
> I have dropped the subscription, recreated and refreshed it many times,
> but it won't move from the initializing phase.
>
> Any suggestions on how to start copying the data again, other than
> dropping the publication and re-creating it?
>
>
> *This correspondence (including any attachments) is for the intended
> recipient(s) only. It may contain confidential or privileged information or
> both. No confidentiality or privilege is waived or lost by any
> mis-transmission. If you receive this correspondence by mistake, please
> contact the sender immediately, delete this correspondence (and all
> attachments) and destroy any hard copies. You must not use, disclose, copy,
> distribute or rely on any part of this correspondence (including any
> attachments) if you are not the intended
> recipient(s).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。*

Re: storing zipped SQLite inside PG ?

[Steve Baldwin]

Could you use the SQLite FDW - https://github.com/pgspider/sqlite_fdw ?

Steve

On Wed, Dec 22, 2021 at 1:27 PM David Gauthier 
wrote:

> Hi:  I need a sanity check (brainstorming) before I jump into coding
> something that might have a better solution known to a community like this
> one.  Here's the situation...
>
> To begin with... PG 11.5 on linux.
> Some code (nature unimportant to this discussion) generates a lot (a lot)
> of data and stuffs it in a SQLite DB which, once the process is complete,
> gets zipped (compression ratio seems to be about 5). We want to keep this
> data in a persistent store which others can get at should they need it.
>  PG seems like a great place to put this especially as the identifying
> context of the SQLite already exists in our PG DB.
>
> So I was thinking about storing the zipped SQLite as a blob in PG.  The
> record it would be added to would give it all the context needed for proper
> retrieval.  After retrieval (in a perl script) I was thinking about writing
> it out to the shell, unzipping it and then opening it using perl/DBI.  The
> metadata of the SQLite could be replicated in a temp table in PG and the
> data content loaded into that for use.  In theory, multiple SQLite DBs
> could be opened like this and then loaded in the same temp tables (as long
> as the metadata is the same... which it will be).
>
> OK, so that's the plan.  What I want to ask this community about is
> whether or not there's a better way to approach this.  Brute force loading
> the SQLite data in regular PG tables would result in billions of records
> and the DB management issues that come with that.  And there's really no
> need to keep all that data immediately accessible like that.  Is there some
> sort of innate PG functionality that would allow me to store and then
> compress my data for targeted retrieval/decompression (even if it's not
> accessible to SQL in that form) ?
>
> OK, you get the picture.  I'm all ears :-)  And thanks in advance for any
> suggestions !
>

Re: Error with Insert from View with ON Conflict

[Steve Baldwin]

I'm pretty sure the 'alias' for the '.. on conflict do update ..' needs to
be 'excluded' (i.e. checks = excluded.checks, ...). Check the docs.

Steve

On Thu, Nov 4, 2021 at 8:05 AM Alex Magnum  wrote:

> Hi,
> I am trying to do an upsert using a view but for some reason get errors.
> All works fine without the ON CONFLICT
>
> INSERT INTO http_stats
> SELECT * FROM view_http_stats AS V WHERE month =date_trunc('month',now())
> ON CONFLICT (url,ip,month) DO UPDATE
>   SET last_update = now(),
>   checks = V.checks,
>   uptime = V.uptime,
>   errors = V.errors;
>
> ERROR:  42P01: missing FROM-clause entry for table "v"
> LINE 5:   checks = V.checks,
>^
> LOCATION:  errorMissingRTE, parse_relation.c:3460
>
> Any help would be appreciated. Thanks in advance.
>
> A
>

What to look for when excessively long commits

[Steve Baldwin]

Hi all,

If I'm seeing instances like this in our logs, what should I look for:

2021-07-06 22:15:34.702
UTC,"bcaas_api","bcaas",8124,"10.122.45.33:46386",60e4d5e6.1fbc,222,"COMMIT",2021-07-06
22:15:02 UTC,37/0,0,LOG,0,"duration: 7128.250 ms",""
2021-07-06 22:15:34.702
UTC,"bcaas_api","bcaas",8483,"10.122.45.33:47274",60e4d5fc.2123,58,"COMMIT",2021-07-06
22:15:24 UTC,3/0,0,LOG,0,"duration: 8419.856 ms",""
2021-07-06 22:15:34.702
UTC,"bcaas_api","bcaas",7665,"10.122.37.247:37530",60e4d5ca.1df1,286,"COMMIT",2021-07-06
22:14:34 UTC,26/0,0,LOG,0,"duration: 8114.358 ms",""
2021-07-06 22:15:34.702
UTC,"bcaas_api","bcaas",6431,"10.122.45.33:42568",60e4d57f.191f,1828,"COMMIT",2021-07-06
22:13:19 UTC,40/0,0,LOG,0,"duration: 8839.643 ms",""
2021-07-06 22:15:34.702
UTC,"bcaas_api","bcaas",8484,"10.122.45.33:47276",60e4d5fc.2124,126,"COMMIT",2021-07-06
22:15:24 UTC,7/0,0,LOG,0,"duration: 8606.898 ms",""
2021-07-06 22:15:34.703
UTC,"bcaas_api","bcaas",8516,"10.122.37.247:41372",60e4d5fe.2144,94,"COMMIT",2021-07-06
22:15:26 UTC,16/0,0,LOG,0,"duration: 7877.650 ms",""

The instance is in AWS RDS. It is a multi-az db.t3.xlarge machine class
running 10.6. It also has a read replica if that could be relevant.

Thanks for any suggestions.

Steve

Re: Internationalisation (i18n) with Postgres as backend

[Steve Baldwin]

Hi Laura,

Did you consider using hstore to store language and data as a kvp? For
example:

b2bc_owner@b2bcreditonline=# create table langtest(pageid text, objectid
text, objectdata hstore, constraint langtest_pk primary key (pageid,
objectid));
CREATE TABLE
b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'abc',
'"en"=>"en for abc","de"=>"de for abc"');
INSERT 0 1
b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'def',
'"en"=>"en for def"');
INSERT 0 1
b2bc_owner@b2bcreditonline=# create or replace function langtestfunc(text,
text, text[]) returns text language sql as $$ select a.data from langtest
as t, unnest(t.objectdata->$3) as a(data) where t.pageid = $1 and
t.objectid = $2 and a.data is not null limit 1 $$;
b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'abc', array['de',
'en']);
 langtestfunc
--
 de for abc
(1 row)
b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'def', array['de',
'en']);
 langtestfunc
--
 en for def
(1 row)

Just a thought.

Cheers,

Steve

On Wed, Jun 2, 2021 at 6:09 AM Laura Smith <
n5d9xq3ti233xiyif...@protonmail.ch> wrote:

> Hi,
>
> I'm creating a Postgres backend for an internal tool which is essentially
> a very simple implementation of multi-lingual CMS.
>
> So far my thoughts are along the lines of the below, but I would
> appreciate a second (or more !) pair of eyes from some Postgresql gurus.  I
> am especially interested in feedback and suggestions in relation to the
> following questions:
>
> (a) Is this going to work as expected (i.e. have I missed some obvious
> foot-guns ?)
>
> (b) Is this manner of doing things reasonably efficient or are there
> better ways I should be thinking of ? (bear in mind the schema is not set
> in stone, so completely out of the box suggestions welcome !).
>
> The basic design concept (oversimplified) is:  For each page, you have one
> or more objects and those objects may have content in one or more languages.
>
> create table langtest(
> pageid text not null,
> objectid text not null ,
> objectlang text not null,
> objectdata text not null);
>
> create unique index on (pageid,objectid,objectlang);
>
> insert into langTest(pageID,objectID,objectLang,objectData) values
> ('zzz','abc','en','Lorem ipsum dolor sit amet');
> insert into langTest(pageID,objectID,objectLang,objectData) values
> ('zzz','abc','de','Amet sit dolor ipsum lorem');
> insert into langTest(pageID,objectID,objectLang,objectData) values
> ('zzz','def','en','Dolor ipsum amet sit lorem');
>
> select distinct on(objectid)objectid,objectlang,pageid,objectdata from
> langTest where pageid='zzz' and objectLang = any('{de,en}'::text[]) order
> by objectid,array_position('{de,en}'::text[],objectLang);
>
> (The idea being that the select query will be wrapped into a function
> which the frontend will call, passing a list of elegible languages as input)
>
> Thanks !
>
> Laura
>
>
>

Re: Row not immediately visible after commit

[Steve Baldwin]

Ok, I believe I have found an explanation, and it is due to a logic error,
not due to anything funky happening with the database. Please excuse the
noise.

Steve

On Tue, Mar 30, 2021 at 11:06 AM Steve Baldwin 
wrote:

> Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their
> postgres-flavoured Aurora product).
>
> b2bc_owner@b2bcreditonline=> select version();
>  version
>
> -
>  PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-11), 64-bit
>
> Steve
>
> On Tue, Mar 30, 2021 at 10:52 AM Adrian Klaver 
> wrote:
>
>> On 3/29/21 4:39 PM, Steve Baldwin wrote:
>> > Hi all,
>> >
>> > I know this is going to sound weird/unbelievable, but I'm trying to
>> come
>> > up with an explanation for what I've observed.
>> >
>> > First, a couple of data points. The instance is running on AWS RDS and
>> > is on version 13.1. All my timestamps and elapsed times were taken from
>> > the postgres log (converted to my local tz).
>> >
>> > 2021-03-30 05:47:40.989+11 Session A begins a new transaction
>> > 2021-03-30 05:47:41.006+11 Session A inserts a single row into table A
>> > 2021-03-30 05:47:41.031+11 Session A inserts two rows into table B
>> > 2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms)
>> >
>> > 2021-03-30 05:47:41.082+11 Session B begins a new transaction
>> > 2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows
>> > from table B
>> > 2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row
>> > from table A using the primary key. Fetch returns zero rows.
>> > 2021-03-30 05:47:41.087+11 Session B aborts the transaction with
>> rollback
>> >
>> > 2021-03-30 05:47:42.143+11 Session C begins a new transaction
>> > 2021-03-30 05:47:42.146+11 Session C fetches the same row as session B
>> above
>> > 2021-03-30 05:47:42.228+11 Session C attempts the same query on table A
>> > as session B above. The fetch returns 1 row, and session C continues
>> > processing.
>> >
>> > I can't imagine how Session B could fail to fetch the row from table A
>> > given that the commit has completed prior to Session B starting its
>> > transaction.
>> >
>> > Any suggestions?
>>
>> Ask AWS support.
>>
>> >
>> > Thanks,
>> >
>> > Steve
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>

Re: Row not immediately visible after commit

[Steve Baldwin]

Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their
postgres-flavoured Aurora product).

b2bc_owner@b2bcreditonline=> select version();
 version
-
 PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-11), 64-bit

Steve

On Tue, Mar 30, 2021 at 10:52 AM Adrian Klaver 
wrote:

> On 3/29/21 4:39 PM, Steve Baldwin wrote:
> > Hi all,
> >
> > I know this is going to sound weird/unbelievable, but I'm trying to come
> > up with an explanation for what I've observed.
> >
> > First, a couple of data points. The instance is running on AWS RDS and
> > is on version 13.1. All my timestamps and elapsed times were taken from
> > the postgres log (converted to my local tz).
> >
> > 2021-03-30 05:47:40.989+11 Session A begins a new transaction
> > 2021-03-30 05:47:41.006+11 Session A inserts a single row into table A
> > 2021-03-30 05:47:41.031+11 Session A inserts two rows into table B
> > 2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms)
> >
> > 2021-03-30 05:47:41.082+11 Session B begins a new transaction
> > 2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows
> > from table B
> > 2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row
> > from table A using the primary key. Fetch returns zero rows.
> > 2021-03-30 05:47:41.087+11 Session B aborts the transaction with rollback
> >
> > 2021-03-30 05:47:42.143+11 Session C begins a new transaction
> > 2021-03-30 05:47:42.146+11 Session C fetches the same row as session B
> above
> > 2021-03-30 05:47:42.228+11 Session C attempts the same query on table A
> > as session B above. The fetch returns 1 row, and session C continues
> > processing.
> >
> > I can't imagine how Session B could fail to fetch the row from table A
> > given that the commit has completed prior to Session B starting its
> > transaction.
> >
> > Any suggestions?
>
> Ask AWS support.
>
> >
> > Thanks,
> >
> > Steve
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Row not immediately visible after commit

[Steve Baldwin]

Hi all,

I know this is going to sound weird/unbelievable, but I'm trying to come up
with an explanation for what I've observed.

First, a couple of data points. The instance is running on AWS RDS and is
on version 13.1. All my timestamps and elapsed times were taken from the
postgres log (converted to my local tz).

2021-03-30 05:47:40.989+11 Session A begins a new transaction
2021-03-30 05:47:41.006+11 Session A inserts a single row into table A
2021-03-30 05:47:41.031+11 Session A inserts two rows into table B
2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms)

2021-03-30 05:47:41.082+11 Session B begins a new transaction
2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows from
table B
2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row
from table A using the primary key. Fetch returns zero rows.
2021-03-30 05:47:41.087+11 Session B aborts the transaction with rollback

2021-03-30 05:47:42.143+11 Session C begins a new transaction
2021-03-30 05:47:42.146+11 Session C fetches the same row as session B above
2021-03-30 05:47:42.228+11 Session C attempts the same query on table A as
session B above. The fetch returns 1 row, and session C continues
processing.

I can't imagine how Session B could fail to fetch the row from table A
given that the commit has completed prior to Session B starting its
transaction.

Any suggestions?

Thanks,

Steve

Re: Using a 'loopback' FDW

[Steve Baldwin]

Thanks so much Tom. That was indeed the problem. In hindsight the error
information makes perfect sense (at least after reading the docs on
pg_hba.conf).

Regards,

Steve

On Wed, Mar 10, 2021 at 3:04 PM Steve Baldwin 
wrote:

> Thanks Tom. I'm running postgres from the standard alpine docker container
> and haven't changed that file. I'm not at my laptop ATM but will check it
> out when I get home.
> Cheers, Steve
>
> On Wed, 10 Mar 2021, 14:42 Tom Lane,  wrote:
>
>> Steve Baldwin  writes:
>> > I'm guessing I'm doing something wrong here. I've used postgres_fdw
>> before
>> > to connect between servers, but in this instance I'm trying to use it
>> > to connect back to itself.
>>
>> What are your pg_hba.conf settings for local connections?
>>
>> > ERROR:  password is required
>> > DETAIL:  Non-superuser cannot connect if the server does not request a
>> > password.
>> > HINT:  Target server's authentication method must be changed or
>> > password_required=false set in the user mapping attributes.
>>
>> This suggests that you're using trust, peer, or something else in which
>> the password isn't actually relevant.
>>
>> regards, tom lane
>>
>

Re: Using a 'loopback' FDW

[Steve Baldwin]

Thanks Tom. I'm running postgres from the standard alpine docker container
and haven't changed that file. I'm not at my laptop ATM but will check it
out when I get home.
Cheers, Steve

On Wed, 10 Mar 2021, 14:42 Tom Lane,  wrote:

> Steve Baldwin  writes:
> > I'm guessing I'm doing something wrong here. I've used postgres_fdw
> before
> > to connect between servers, but in this instance I'm trying to use it
> > to connect back to itself.
>
> What are your pg_hba.conf settings for local connections?
>
> > ERROR:  password is required
> > DETAIL:  Non-superuser cannot connect if the server does not request a
> > password.
> > HINT:  Target server's authentication method must be changed or
> > password_required=false set in the user mapping attributes.
>
> This suggests that you're using trust, peer, or something else in which
> the password isn't actually relevant.
>
> regards, tom lane
>

Using a 'loopback' FDW

[Steve Baldwin]

I'm guessing I'm doing something wrong here. I've used postgres_fdw before
to connect between servers, but in this instance I'm trying to use it
to connect back to itself.

(This is postgres 13.2)

In my local DB have a user 'slaw_owner' which has a password of 'password'.
This user has been granted usage on postgres_fdw.

slaw_owner@slaw=> \des+
   List of foreign servers
 Name | Owner | Foreign-data wrapper | Access privileges | Type | Version |
FDW options | Description
--+---+--+---+--+-+-+-
(0 rows)

slaw_owner@slaw=> create server caas foreign data wrapper postgres_fdw
options(dbname 'slaw', host 'localhost');
CREATE SERVER
slaw_owner@slaw=> create user mapping for slaw_owner server caas options
(user 'slaw_owner', password 'password');
CREATE USER MAPPING

So far so good. When I try to use this mapping however

slaw_owner@slaw=> import foreign schema caas limit to (api_key, buyer_user)
from server caas into fdw;
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a
password.
HINT:  Target server's authentication method must be changed or
password_required=false set in the user mapping attributes.

When I look at the server (csv) log it _seems_ to be connecting
successfully:

2021-03-10 02:49:22.100
UTC,"slaw_owner","slaw",167,"172.20.0.1:63186",6048331e.a7,9,"idle",2021-03-10
02:46:54 UTC,3/106,0,LOG,0,"statement: import foreign schema caas limit
to (api_key, buyer_user) from server caas into fdw;","psql","client
backend"
2021-03-10 02:49:22.104
UTC,,,667,"127.0.0.1:54290",604833b2.29b,1,"",2021-03-10
02:49:22 UTC,,0,LOG,0,"connection received: host=127.0.0.1
port=54290","","not initialized"
2021-03-10 02:49:22.106
UTC,"slaw_owner","slaw",667,"127.0.0.1:54290",604833b2.29b,2,"authentication",2021-03-10
02:49:22 UTC,4/17,0,LOG,0,"connection authorized: user=slaw_owner
database=slaw application_name=postgres_fdw","","client backend"
2021-03-10 02:49:22.109
UTC,"slaw_owner","slaw",167,"172.20.0.1:63186",6048331e.a7,10,"IMPORT
FOREIGN SCHEMA",2021-03-10 02:46:54 UTC,3/106,0,ERROR,2F003,"password is
required","Non-superuser cannot connect if the server does not request a
password.","Target server's authentication method must be changed or
password_required=false set in the user mapping attributes.""import
foreign schema caas limit to (api_key, buyer_user) from server caas into
fdw;",,,"psql","client backend"
2021-03-10 02:49:22.111
UTC,"slaw_owner","slaw",667,"127.0.0.1:54290",604833b2.29b,3,"idle",2021-03-10
02:49:22 UTC,,0,LOG,0,"disconnection: session time: 0:00:00.008
user=slaw_owner database=slaw host=127.0.0.1
port=54290","postgres_fdw","client backend"

If, as a superuser I modify the user mapping, everything works:

[~/git/slaw]$ psql -U dba
psql (13.2)
Type "help" for help.

dba@slaw=# alter user MAPPING FOR slaw_owner server caas options (add
password_required 'false');
ALTER USER MAPPING
dba@slaw=#
\q
[~/git/slaw]$ psql
psql (13.2)
Type "help" for help.

slaw_owner@slaw=> import foreign schema caas limit to (api_key, buyer_user)
from server caas into fdw;
IMPORT FOREIGN SCHEMA

I don't understand why it doesn't like it when I define a password in the
user mapping.

Any ideas gratefully received.

Thanks,

Steve

Re: Consequence of changes to CTE's in 12

[Steve Baldwin]

Thanks Tom. This optimization fences concept is a new one to me, so great
to know about.

This does indeed give me a nice version-independent solution, and make me a
very happy camper ;-)

Steve

On Fri, Feb 12, 2021 at 11:45 AM Tom Lane  wrote:

> Steve Baldwin  writes:
> > Is there a chance that the query optimiser should 'notice' the
> > pg_try_advisory_xact_lock function, and not be so clever when it sees it?
>
> The general policy with respect to volatile functions in WHERE quals is
> "here be dragons".  You don't have enough control over when a WHERE clause
> will be evaluated to be sure about what the semantics will be; and we
> don't want to tie the optimizer's hands to the extent that would be needed
> to make it fully predictable.
>
> In this particular case, you can make it fairly safe by making sure there
> are optimization fences both above and below where the WHERE clause is.
> You have one above from the LIMIT 1, but (with the new interpretation of
> CTEs) not one below it.  Adding a fence -- either OFFSET 0 or LIMIT ALL --
> to the first CTE should fix it in a reasonably version-independent
> fashion.
>
> regards, tom lane
>

Re: Consequence of changes to CTE's in 12

[Steve Baldwin]

Thanks all. The fact that this is a view is not really relevant. I only
bundled as a view here to make testing simpler. The underlying query still
behaves differently pre-12 and 12+.

Is there a chance that the query optimiser should 'notice' the
pg_try_advisory_xact_lock function, and not be so clever when it sees it?

It makes me wonder what other queries we might have that are inadvertently
relying on the default materializing behaviour of pre-12.

Steve

On Fri, Feb 12, 2021 at 11:24 AM Michael Lewis  wrote:

> This functionality seems more a candidate for a set-returning function
> rather than a view, but I like my views to be side effect free and read
> only. It would be trivial to implement in plpgsql I believe.
>
> If you move the limit 1 to the first CTE, does it not give you the same
> behavior in both versions?
>
>>

Consequence of changes to CTE's in 12

[Steve Baldwin]

Hi,

I realise this is probably an edge case, but would appreciate some advice
or suggestions.

I have a table that has rows to be processed:

postgres=# create table lock_test (id uuid primary key default
gen_random_uuid(), lock_id bigint);
CREATE TABLE
postgres=# insert into lock_test (lock_id) values
(10),(10),(20),(30),(30),(30);
INSERT 0 6
postgres=#* select * from lock_test;
  id  | lock_id
--+-
 326a2d34-ecec-4c01-94bb-40f43f244d40 |  10
 8ed1d680-6304-4fb2-a47c-9427c6d48622 |  10
 04482ba1-7193-4e7f-a507-71fe6a351781 |  20
 34003468-e959-4c7b-a48c-97195c43982e |  30
 2d20394b-c79b-4867-8d0a-72044c370543 |  30
 bef7b880-e7a0-4c07-8eab-182c9c1bd33a |  30
(6 rows)

My business rule says I need to process rows by lock_id in descending order
of the number of rows. In my test data, that would mean rows with a lock_id
of 30 would be processed first.

If another 'processor' wakes up while lock_id 30 is being processed, it
moves on to lock_id 10, etc.

My pre-12 solution was a view something like this:

postgres=# create or replace view lock_test_v
as
with g as (
  select lock_id, count(*) as n_rows
  from lock_test
  group by lock_id
  order by n_rows desc
  ), l as (
  select lock_id
  from g
  where pg_try_advisory_xact_lock(lock_id)
  limit 1)
select t.*
from lock_test as t
join l on t.lock_id = l.lock_id
;
CREATE VIEW

This works fine, and only creates one advisory lock (or zero) when querying
the view:

postgres=# begin;
BEGIN
postgres=#* select classid, objid from pg_locks where locktype = 'advisory'
and pid = pg_backend_pid();
 classid | objid
-+---
(0 rows)

postgres=#* select * from lock_test_v;
  id  | lock_id
--+-
 34003468-e959-4c7b-a48c-97195c43982e |  30
 2d20394b-c79b-4867-8d0a-72044c370543 |  30
 bef7b880-e7a0-4c07-8eab-182c9c1bd33a |  30
(3 rows)

postgres=#* select classid, objid from pg_locks where locktype = 'advisory'
and pid = pg_backend_pid();
 classid | objid
-+---
   0 |30
(1 row)

However in 12, the same view returns the same data, but generates multiple
advisory locks:

sns_publisher=# begin;
BEGIN
sns_publisher=#* select version();
version
---
 PostgreSQL 12.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine
10.2.1_pre1) 10.2.1 20201203, 64-bit
(1 row)

sns_publisher=#* select classid, objid from pg_locks where locktype =
'advisory' and pid = pg_backend_pid();
 classid | objid
-+---
(0 rows)

sns_publisher=#* select * from lock_test_v;
  id  | lock_id
--+-
 1a9f3f77-fcdc-4779-8fd9-30f274825e15 |  30
 ac670997-9c23-44da-8eb8-e055f02a5f19 |  30
 b5f939ac-7c7d-4975-811a-9af26aaa3a31 |  30
(3 rows)

sns_publisher=#* select classid, objid from pg_locks where locktype =
'advisory' and pid = pg_backend_pid();
 classid | objid
-+---
   0 |20
   0 |30
   0 |10
(3 rows)

If I use 'as materialized' for my 'g' cte, I get the same outcome as with
pre-12 versions.

My 'dilemma' is that this functionality is packaged and the database it is
bundled into could be running on a pre-12 version or 12+. Is there any way
I can rewrite my view to achieve the same outcome (i.e. only creating 0 or
1 advisory locks) regardless of the server version? I realise I could have
two installation scripts but if it is installed into a pre-12 DB and that
DB is subsequently upgraded to 12+, my behaviour is broken.

Any suggestions greatly appreciated.

Steve

Re: Problem with trigger function

[Steve Baldwin]

David, from what I can see of the docs, for 9.6 it is PROCEDURE.  It seems
FUNCTION didn't appear until 11.

Steve

On Fri, Feb 12, 2021 at 7:05 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
> On Thursday, February 11, 2021, Steve Baldwin 
> wrote:
>
>> Try ... EXECUTE PROCEDURE customer_num_informix()
>>
>
>
> FUNCTION, not PROCEDURE
>
> David J.
>

Re: Problem with trigger function

[Steve Baldwin]

Try ... EXECUTE PROCEDURE customer_num_informix()

Steve

On Fri, Feb 12, 2021 at 6:47 AM James B. Byrne 
wrote:

> I am trying to implement a trigger in a PostgreSQL-9.6.17 database:
>
> CREATE OR REPLACE FUNCTION customer_num_informix()
>   RETURNS trigger AS $$
> BEGIN
>   -- An Aubit/Informix 4GL INSERT statement passes the value 0 to Informix
>   -- DBMS for columns that have the SERIAL data type.  Informix will then
>   -- use the incremented serial number in place of 0. PostgreSQL instead
>   -- will simply take the value 0 and replace the incremented serial
> number.
>   -- This trigger function emulates the Informix DBMS behaviour.
>   --
>   -- The NEW variable contains the data for the row to be INSERTed or
>   -- UPDATEd. It, and its complement OLD used for UPDATE and DELETE,
>   -- are automatically created and populated by PostgreSQL whenever
>   -- a data-change function is called.
>   --
>   IF NEW.customer_num = 0 THEN
> SELECT nextval('customer_customer_num_seq') INTO
> NEW.customer_customer_num;
>   ELSE
> IF NEW.customer_customer_num > 0 THEN
>   PERFORM setval('customer_customer_num_seq',
> NEW.customer_customer_num);
> END IF;
>   END IF;
>   RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> ALTER FUNCTION public.customer_num_informix() OWNER TO hll_4gl_testing;
>
> CREATE TRIGGER customer_num_serial
>   BEFORE INSERT ON customer
> FOR EACH ROW EXECUTE customer_num_informix();
>
> The problem is that I am getting a syntax error on the CREATE TRIGGER
> statement:
>
> ERROR:  syntax error at or near "customer_num_informix"
> LINE 3: FOR EACH ROW EXECUTE customer_num_informix();
>
> I do not see what the error is. What is wrong with the syntax I used?
>
> --
> ***  e-Mail is NOT a SECURE channel  ***
> Do NOT transmit sensitive data via e-Mail
>Unencrypted messages have no legal claim to privacy
>  Do NOT open attachments nor follow links sent by e-Mail
>
> James B. Byrnemailto:byrn...@harte-lyne.ca
> Harte & Lyne Limited  http://www.harte-lyne.ca
> 9 Brockley Drive  vox: +1 905 561 1241
> Hamilton, Ontario fax: +1 905 561 0757
> Canada  L8E 3C3
>
>
>
>

Re: FDW connections

[Steve Baldwin]

Thanks guys. I realise it was an odd request. The scenario is I'm building
a mechanism for an application to operate in limited capacity using a
secondary database while the primary database is being upgraded. I'm using
postgres_fdw to sync changes between the primary and secondary databases.
The reason for the question was during my testing I was switching between
'modes' (we refer to them as online and offline), and during the secondary
database setup process, it renames a database if it exists. That was
failing due to an existing connection that ended up being from the primary
database during its 'sync-from-offline' process from the previous test. The
primary database connection still existed because it was made from a
connection pool. So, the bottom line is that this was a somewhat contrived
situation, and I was able to release the connection from the pool after
performing the fdw query.

 We're using AWS RDS, so we've had to implement our own 'zero-downtime'
functionality. RDS also means we're a bit behind version-wise. The latest
version we have available today is 12.5, so I imagine it will be quite a
while before PG14 is a possibility.

Thanks very much for your help.

Kind regards,

Steve

On Fri, Jan 22, 2021 at 7:32 PM Hou, Zhijie 
wrote:

> > > If I have made a query on a foreign table (using postgres_fdw),  it
> > > establishes a connection automatically. Is there any way to
> > > disconnect that fdw connection without disconnecting the session  that
> > > instigated it?
> >
> > No.
> >
> > From PostgreSQL v14 on, there is the "idle_session_timeout" that you
> could
> > set on the server to close such sessions.  postgresql_fdw will silently
> > re-establish such broken connections.  You could set this parameter in
> the
> > foreign server definition.
> >
> > But this is a strange request: why would you want to close such
> connections
> > before the database session ends?
> >
>
> Hi
>
> There are two new functions being reviewed called:
>
> postgres_fdw_disconnect()
> postgres_fdw_disconnect_all()
>
> These function may solve your problem,
> If you are interested in that, you can take a look at [1].
>
> The functions have not been committed yet, it may can be used in PG14.
>
> [1]
> https://www.postgresql.org/message-id/CALj2ACVcpU%3DwB7G%3DzT8msVHvPs0-y0BbviupiT%2Bf3--bGYaOMA%40mail.gmail.com
>
>
> Best regards,
> houzj
>
>
>
>
>
>
>
>

FDW connections

[Steve Baldwin]

Hi all,

If I have made a query on a foreign table (using postgres_fdw), it
establishes a connection automatically. Is there any way to disconnect that
fdw connection without disconnecting the session that instigated it?

Thanks,

Steve

Re: Extracting data from jsonb array?

[Steve Baldwin]

This article might help understanding the reason -
https://dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b

>From the postgres docs:

"When GROUP BY is present, or any aggregate functions are present, it is
not valid for the SELECT list expressions to refer to ungrouped columns
except within aggregate functions or when the ungrouped column is
functionally dependent on the grouped columns, since there would otherwise
be more than one possible value to return for an ungrouped column. A
functional dependency exists if the grouped columns (or a subset thereof)
are the primary key of the table containing the ungrouped column."

If you come from an Oracle background (as I do), this behaviour may
surprise you, since Oracle definitely doesn't allow this.

I much prefer Postgres. 

Steve

On Tue, Dec 8, 2020 at 3:32 PM Ken Tanzer  wrote:

>
>
> On Mon, Dec 7, 2020 at 8:16 PM Tom Lane  wrote:
>
>> Ken Tanzer  writes:
>> >> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f,
>> >> jsonb_to_recordset(js) as t(key2 text) group by f.id;
>>
>> > After a little more thought and experimenting, I'm not so sure about
>> this
>> > part.  In particular, I'm not clear why Postgres isn't complaining about
>> > the f1 and f2 fields.  (It's not giving the "must appear in the GROUP BY
>> > clause or be used in an aggregate function" error that I would expect,
>> and
>> > that I am getting when I try to apply this to my real query.)
>>
>> > Can anyone explain to me why those fields don't need to be grouped?
>> Thanks.
>>
>>
>> If foo.id isn't a primary key, then I'm confused too.  Can we see the
>> full declaration of the table?
>>
>>
> So I created some confusion because the original version of the table in
> my example did _not_ declare a primary key.  A later example, and the one I
> used, did have the primary key:
>
> CREATE TEMP TABLE foo (
>   id INTEGER PRIMARY KEY,
>   f1  TEXT,
>   f2  TEXT,
>   js  JSONB
> );
>
>
>
> If foo.id is a primary key, it knows that the "group by" doesn't really
>> merge any rows of foo, so it lets you get away with that.  I think this
>> is actually required by spec, but am too lazy to go check right now.
>
>
> If I do that without the Primary Key, it does indeed complain about f1 &
> f2 not being grouped.  But what is the "It" in "it lets you get away with
> that" referring to?  Or more specifically, is this some specialized case
> because of something related to use of the jsonb_recordset function?  I've
> gotten so used to having to group on every non-aggregate field that I
> didn't realize there could be any exception to that.
>
> Thanks!
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: Extracting data from jsonb array?

[Steve Baldwin]

You can also do this:

b2bcreditonline=# select f.id, f.f1, f.f2, (select array_agg(t.key2) from
jsonb_to_recordset(js) as t(key2 text)) as key2s from foo as f;
 id |f1 | f2 |   key2s
+---++
  1 | My text 1 | My text 1a | {r1k2val,r1k2val2}
  2 | My text 2 | My text 2a | {r2k2val,r2k2val2}
(2 rows)


On Tue, Dec 8, 2020 at 3:09 PM Steve Baldwin 
wrote:

> What am I missing?
>
> b2bcreditonline=# select * from foo;
>  id |   js
>   |f1 | f2
>
> ++---+
>   1 | [{"key": "r1kval", "key2": "r1k2val"}, {"key": "r1kval2", "key2":
> "r1k2val2"}] | My text 1 | My text 1a
>   2 | [{"key": "r2kval", "key2": "r2k2val"}, {"key": "r2kval2", "key2":
> "r2k2val2"}] | My text 2 | My text 2a
> (2 rows)
>
> b2bcreditonline=# select f.id, f.f1, f.f2, array_agg(t.key2) from foo as
> f, jsonb_to_recordset(js) as t(key2 text) group by f.id;
> ERROR:  column "f.f1" must appear in the GROUP BY clause or be used in an
> aggregate function
> LINE 1: select f.id, f.f1, f.f2, array_agg(t.key2) from foo as f, js...
>  ^
>
> On Tue, Dec 8, 2020 at 2:57 PM Ken Tanzer  wrote:
>
>>
>>
>> On Mon, Dec 7, 2020 at 7:33 PM Ken Tanzer  wrote:
>>
>>>
>>> But this has a big advantage in that you can just add other fields to
>>> the query, thusly:
>>>
>>> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f,
>>> jsonb_to_recordset(js) as t(key2 text) group by f.id;
>>>  id |f1 | f2 | array_agg
>>> +---++
>>>   2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
>>>   1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
>>> (2 rows)
>>>
>>
>> After a little more thought and experimenting, I'm not so sure about this
>> part.  In particular, I'm not clear why Postgres isn't complaining about
>> the f1 and f2 fields.  (It's not giving the "must appear in the GROUP BY
>> clause or be used in an aggregate function" error that I would expect, and
>> that I am getting when I try to apply this to my real query.)
>>
>> Can anyone explain to me why those fields don't need to be grouped?
>> Thanks.
>>
>> Ken
>>
>>
>>
>>> --
>>> AGENCY Software
>>> A Free Software data system
>>> By and for non-profits
>>> *http://agency-software.org/ <http://agency-software.org/>*
>>> *https://demo.agency-software.org/client
>>> <https://demo.agency-software.org/client>*
>>> ken.tan...@agency-software.org
>>> (253) 245-3801
>>>
>>> Subscribe to the mailing list
>>>  to
>>> learn more about AGENCY or
>>> follow the discussion.
>>>
>>
>>
>> --
>> AGENCY Software
>> A Free Software data system
>> By and for non-profits
>> *http://agency-software.org/ <http://agency-software.org/>*
>> *https://demo.agency-software.org/client
>> <https://demo.agency-software.org/client>*
>> ken.tan...@agency-software.org
>> (253) 245-3801
>>
>> Subscribe to the mailing list
>>  to
>> learn more about AGENCY or
>> follow the discussion.
>>
>

Re: Extracting data from jsonb array?

[Steve Baldwin]

What am I missing?

b2bcreditonline=# select * from foo;
 id |   js
  |f1 | f2
++---+
  1 | [{"key": "r1kval", "key2": "r1k2val"}, {"key": "r1kval2", "key2":
"r1k2val2"}] | My text 1 | My text 1a
  2 | [{"key": "r2kval", "key2": "r2k2val"}, {"key": "r2kval2", "key2":
"r2k2val2"}] | My text 2 | My text 2a
(2 rows)

b2bcreditonline=# select f.id, f.f1, f.f2, array_agg(t.key2) from foo as f,
jsonb_to_recordset(js) as t(key2 text) group by f.id;
ERROR:  column "f.f1" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select f.id, f.f1, f.f2, array_agg(t.key2) from foo as f, js...
 ^

On Tue, Dec 8, 2020 at 2:57 PM Ken Tanzer  wrote:

>
>
> On Mon, Dec 7, 2020 at 7:33 PM Ken Tanzer  wrote:
>
>>
>> But this has a big advantage in that you can just add other fields to the
>> query, thusly:
>>
>> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f,
>> jsonb_to_recordset(js) as t(key2 text) group by f.id;
>>  id |f1 | f2 | array_agg
>> +---++
>>   2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
>>   1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
>> (2 rows)
>>
>
> After a little more thought and experimenting, I'm not so sure about this
> part.  In particular, I'm not clear why Postgres isn't complaining about
> the f1 and f2 fields.  (It's not giving the "must appear in the GROUP BY
> clause or be used in an aggregate function" error that I would expect, and
> that I am getting when I try to apply this to my real query.)
>
> Can anyone explain to me why those fields don't need to be grouped?
> Thanks.
>
> Ken
>
>
>
>> --
>> AGENCY Software
>> A Free Software data system
>> By and for non-profits
>> *http://agency-software.org/ *
>> *https://demo.agency-software.org/client
>> *
>> ken.tan...@agency-software.org
>> (253) 245-3801
>>
>> Subscribe to the mailing list
>>  to
>> learn more about AGENCY or
>> follow the discussion.
>>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: Extracting data from jsonb array?

[Steve Baldwin]

How about this:

b2bcreditonline=# select f.id, array_agg(t.key2) from foo as f,
jsonb_to_recordset(js) as t(key2 text) group by f.id;
 id | array_agg
+
  2 | {r2k2val,r2k2val2}
  1 | {r1k2val,r1k2val2}
(2 rows)

Steve

On Tue, Dec 8, 2020 at 1:00 PM David G. Johnston 
wrote:

> On Monday, December 7, 2020, Ken Tanzer  wrote:
>
>>
>>
>> I'm of course very glad Postgresql has the ability to work with JSON at
>> all, but as I dig into it I'm kinda surprised at the level of complexity
>> needed to extract data in relatively simple ways.  Hopefully eventually it
>> will seem simple to me, as it seems to appear to others.
>>
>
> Upgrade to v12+ for access to simpler/cleaner.  Composing various
> unnesting and key extraction operations works but, yes, it gets ugly
> proportional to the extent you need to dig into complex json structures.
> That said lateral joining reduces nesting which is measurably cleaner.
>
> David J.
>
>

Re: Extracting data from jsonb array?

[Steve Baldwin]

Try:

select _message_body->'Charges'->>'Name' from ...

Steve

On Tue, Dec 8, 2020 at 9:58 AM Ken Tanzer  wrote:

> Hello.  This is probably simple, but I'm having a hard time making use of
> some json data, and hoping someone can help.
>
> Given some data that looks like this  (I added a couple of carriage
> returns for readability):
>
> SELECT _message_body->'Charges' FROM message_import_court_case where
> _message_exchange_id=1296;
>
>
>   ?column?
>
>
>
>
> ---
>
> --
>  [
> {"NCIC": {"Code": null, "Name": null, "Behavior": null}, "Name":
> "Possession Of Burglary Tools", "OffenseCodes": "9A52030;9A52060",
> "ClassSeverity": {"Code": "M|GM", "Description": null}},
> {"NCIC": {"Code": null, "Name": null, "Behavior": null}, "Name": "Burglary
> In The Second Degree (Commercial)", "OffenseCodes": "9A52030",
> "ClassSeverity": {"Code": "F|B", "Description": null}}
> ]
>
> How can I extract the two "Name" elements?  (i.e.:
>
> Possession of Burglary Tools
> Burglary In The Second Degree (Commercial)
>
> This is with 9.6.20.  Thanks in advance!
>
> Ken
>
>
>
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: SELECT but only if not present in another table

[Steve Baldwin]

Can't you just use table aliases? So, the outer word_moves would become
'word_moves as wm', word_puzzles would become 'word_puzzles as wp', and the
where clause 'WHERE wp.mid = wm.mid' ?

hth,

Steve

On Mon, Dec 7, 2020 at 4:08 AM Alexander Farber 
wrote:

> Good evening,
>
> in PostgreSQL 13.1 I save player moves in the table:
>
> # \d words_moves
>   Table "public.words_moves"
>  Column  |   Type   | Collation | Nullable |
>Default
>
> -+--+---+--+--
>  mid | bigint   |   | not null |
> nextval('words_moves_mid_seq'::regclass)
>  action  | text |   | not null |
>  gid | integer  |   | not null |
>  uid | integer  |   | not null |
>  played  | timestamp with time zone |   | not null |
>  tiles   | jsonb|   |  |
>  score   | integer  |   |  |
>  str | text |   |  |
>  hand| text |   |  |
>  letters | character(1)[]   |   |  |
>  values  | integer[]|   |  |
> Indexes:
> "words_moves_pkey" PRIMARY KEY, btree (mid)
> "words_moves_gid_played_idx" btree (gid, played DESC)
> "words_moves_puzzle_idx" btree (puzzle)
> "words_moves_uid_action_played_idx" btree (uid, action, played)
> "words_moves_uid_idx" btree (uid)
> Check constraints:
> "words_moves_score_check" CHECK (score >= 0)
> Foreign-key constraints:
> "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
> ON DELETE CASCADE
> "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
> ON DELETE CASCADE
> Referenced by:
> TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
> TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>
> Some of the moves can be "interesting" in the sense that the player have
> used all 7 letter tiles or achieved a high score over 90 points,
>
> I want to display those moves as "puzzles" and have prepared a table to
> store, per-user, who has solved them:
>
> # \d words_puzzles
> Table "public.words_puzzles"
>  Column |   Type   | Collation | Nullable | Default
> +--+---+--+-
>  mid| bigint   |   | not null |
>  uid| integer  |   | not null |
>  solved | timestamp with time zone |   | not null |
> Foreign-key constraints:
> "words_puzzles_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
> ON DELETE CASCADE
> "words_puzzles_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
> ON DELETE CASCADE
>
> Now I am trying to create a custom stored function which would return just
> one mid (move id) which is not too new (1 year old) and the user has not
> tackled it yet:
>
> CREATE OR REPLACE FUNCTION words_daily_puzzle(
> in_uid   int
> ) RETURNS table (
> out_mid  bigint,
> out_secret   text
> ) AS
> $func$
> SELECT
> mid,
> MD5(mid ||'my little secret')
> FROM words_moves
> WHERE action = 'play'
> AND (LENGTH(str) = 7 OR score > 90)
> AND played BETWEEN CURRENT_TIMESTAMP - INTERVAL '51 week' AND
> CURRENT_TIMESTAMP - INTERVAL '50 week'
> -- the user has not solved this puzzle yet
> AND NOT EXISTS (SELECT 1 FROM words_puzzles WHERE mid =
> the_outer_mid AND uid = in_uid)
> ORDER BY PLAYED ASC
> LIMIT 1;
> $func$ LANGUAGE sql;
>
> As you can see I am missing 1 piece - how do I address the outer SELECT
> mid from the EXISTS-SELECT?
>
> I have written "the_outer_mid" there.
>
> Should I use LEFT JOIN LATERAL here? I have difficulties wrapping my head
> around this.
>
> TLDR: how to return 1 mid from 1 year ago, which is not solved by the user
> in_uid yet?
>
> Thank you
> Alex
>
>
>

Locking and postgres_fdw extension

[Steve Baldwin]

Hi all,

I have two DB instances - 'online' and 'offline'. In 'offline' I have a
foreign table 'f' imported from 'online'. I want to execute a query from
'offline' to obtain a row lock, and this works fine:

select id from f where id = 1 for no key update;

However if I want it to fail immediately if unable to obtain the lock, it
seems nowait is ignored for foreign tables (or at least with my version of
postgres_fdw). The waiting session blocks until the holding session commits
or rolls back.

My 'workaround' was to create a view in 'online':

create or replace view f_lock as select * from f for no key update nowait;

Then use import foreign schema to bring that view into 'offline'. I can
then execute:

select id from f_lock where id = 1;

and it will fail immediately if the row is already locked.

Is there a better way to do this?

Thanks,

Steve

Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?

[Steve Baldwin]

Hi Alex,

Try something like this:

b2bc_dev=# select jsonb_insert('{"hello": "world"}'::jsonb,
'{uid}'::text[], to_jsonb(1));
 jsonb_insert
--
 {"uid": 1, "hello": "world"}
(1 row)

Steve

On Sun, Sep 13, 2020 at 6:55 AM Alexander Farber 
wrote:

> Good evening,
>
> I am trying to take a JSONB object (comes from an HTTP cookie set by my
> app) and add a property "uid" to it, which should hold an integer:
>
> CREATE OR REPLACE FUNCTION words_get_user(
> in_users jsonb,
> OUT out_user jsonb
> ) RETURNS jsonb AS
> $func$
> DECLARE
> _user   jsonb;
> _uidinteger;
> _banned boolean;
> _removedboolean;
> BEGIN
> -- in_users must be a JSON array with at least 1 element
> IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
> RAISE EXCEPTION 'Invalid users = %', in_users;
> END IF;
>
> -- ensure that every record has a valid auth
> FOR _user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
> LOOP
> IF NOT words_valid_user((_user->>'social')::int,
>  _user->>'sid',
>  _user->>'auth') THEN
> RAISE EXCEPTION 'Invalid user = %', _user;
> END IF;
>
> IF out_user IS NULL THEN
> SELECT
> uid,
> u.banned_until > CURRENT_TIMESTAMP,
> u.removed
> INTO STRICT
> _uid,
> _banned,
> _removed
> FROM words_social s
> LEFT JOIN words_users u USING(uid)
> WHERE s.social = (_user->>'social')::int
> AND s.sid = _user->>'sid';
>
> IF _banned THEN
> RAISE EXCEPTION 'Banned user = %', _user;
> END IF;
>
> IF _removed THEN
> RAISE EXCEPTION 'Removed user = %', _user;
> END IF;
>
> out_user := JSONB_INSERT(_user, '{uid}', _uid);
> END IF;
> END LOOP;
> END
> $func$ LANGUAGE plpgsql;
>
> Unfortunately, when I run my stored function it fails:
>
> words_en=> select out_user from
> words_get_user('[{"given":"Abcde1","social":1,"auth":"xxx","stamp":1480237061,"sid":"a","photo":"
> https://vk.com/images/camera_200.png
> "},{"given":"Abcde2","social":2,"auth":"xxx","stamp":1477053188,"sid":"a"},{"given":"Abcde3","social":3,"auth":"xxx","stamp":1477053330,"sid":"a"}]'::jsonb);
> ERROR:  function jsonb_insert(jsonb, unknown, integer) does not exist
> LINE 1: SELECT JSONB_INSERT(_user, '{uid}', _uid)
>^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY:  SELECT JSONB_INSERT(_user, '{uid}', _uid)
> CONTEXT:  PL/pgSQL function words_get_user(jsonb) line 44 at assignment
>
> What is missing here please?
>
> Thank you
> Alex
>
>

Re: Transaction prevention

[Steve Baldwin]

Thanks Tom. The idle_in_transaction_session_timeout could work well, but it
seems to be just a default that can be overridden by a user post-login (or
am I missing something?). I'm thinking of setting lock_timeout as part of
the migration process so it will fail if it is unable to obtain a lock in a
'reasonable' amount of time. I wonder what other folks do?

Cheers,

Steve

On Thu, Jul 30, 2020 at 10:23 AM Tom Lane  wrote:

> Steve Baldwin  writes:
> > If I have a user that is restricted to select access (only) on a single
> > table, is there any way to prevent that user from starting a transaction?
>
> No, but maybe setting statement_timeout and/or
> idle_in_transaction_session_timeout for that user would be helpful
> (cf ALTER USER ... SET ...).
>
> regards, tom lane
>

Transaction prevention

[Steve Baldwin]

Hi,

If I have a user that is restricted to select access (only) on a single
table, is there any way to prevent that user from starting a transaction?

The reason for the question is that the select-only user can block another
session trying to run an alter table on that table if the select-only
user runs their query in a transaction.

This happened to us recently where a migration script that was being run
during a ci deployment 'hung' until the select-only user happened to
disconnect. I'm wondering if we need to terminate any sessions from
select-only users at the start of the migration, or if there is a better
way.

Thanks,

Steve

Re: Q: Comparing PostgreSQL and Oracle

[Steve Baldwin]

For what it's worth, I used Oracle daily as a DBA and developer for my job
from 1983 until around 3 years ago when Postgres was chosen for a project I
was assigned to. I became pretty familiar with the workings of Oracle and
was somewhat skeptical when told we would be using Postgres, however it has
honestly been like a breath of fresh air. It has all the transactional
behaviour of a 'proper' RDBMS (even transactional DDL which as far as I'm
aware is still not supported in Oracle), and has all the features a
developer actually cares about. Postgres runs beautifully either locally on
a laptop, or in a Docker container using a tiny fraction of the resources
Oracle would need to do the same thing, so makes true local full-stack
development a delight. As someone familiar with both, I can honestly say go
with Postgres. You won't regret it.

Steve

On Wed, May 20, 2020 at 6:08 PM Ahmed, Nawaz (Fuji Xerox Australia) <
nawaz.ah...@aus.fujixerox.com> wrote:

> Hi,
>
> With massive development within OSS PosgreSQL over the years, there is
> plenty of things it can do when compared to Oracle. The below URLs give you
> a comprehensive comparison table, I am sure you or your colleagues may have
> already stumbled upon these URLs. In addition to that, there is something
> called "oracle compabilities" for PostgreSQL which makes it more like
> Oracle for most of the Oracle's native functionalities like packages and
> procedures  etc.
>
> https://db-engines.com/en/system/Oracle%3BPostgreSQL
>
> https://db-engines.com/en/system/MySQL%3BOracle%3BPostgreSQL
>
>
>
>
> -Original Message-
> From: Alfonso 
> Sent: Wednesday, 20 May 2020 7:56 AM
> To: pgsql-general@lists.postgresql.org
> Subject: Q: Comparing PostgreSQL and Oracle
>
> Hi colleagues.
>
>
> I'm working in a Java application with some colleagues and we are in doubt
> wether to use Oracle or PostgreSQL as the data store. It will be a OLTP
> mainly application.
>
> Beside of license terms/costs  which is a clear point in favor of
> PostgreSQL, could you please help me to get some more details regarding
> what you can do in PostgreSQL and you can't in Oracle oand viceversa, and
> what can be done using both but it is better done by PostgreSQL vs Oracle
> and so on?
>
> I mean, about functionalities, performance, support costs if any and
> qualtity, etc.
>
>
> Many thanks.
>
> Best Regards.
>
>
>
>
>
> IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments,
> is private and confidential and may contain legally privileged information.
> It is for the addressee's attention only. If you are not the intended
> recipient and have received this transmission, you must not use, edit,
> print, copy or disclose its contents to any person or disseminate the
> information contained herein or hereto attached, and you must notify sender
> immediately by return email and delete this transmission from your system.
> Any confidentiality, privilege or copyright is not waived or lost because
> this e-mail has been sent to you in error. We have used reasonable efforts
> to protect this transmission from computer viruses and other malicious
> software, but no warranty is made and the sender takes no responsibility
> for any loss or damage incurred from using this email or the information
> contained in this email.
>

Re: timestamp and timestamptz

[Steve Baldwin]

I'm pretty sure you are mistaken. Postgres doesn't store the 'creating'
time zone in a timestamptz column.

Try doing this before re-running your test:

set timezone to 'utc';

What you are seeing in your test is an artifact of that timezone setting.

Steve

On Thu, Apr 16, 2020 at 5:19 PM raf  wrote:

> David G. Johnston wrote:
>
> > On Wed, Apr 15, 2020 at 4:53 PM raf  wrote:
> >
> > > I don't see much difference in storing a timestamptz in UTC or a
> > > timestamptz
> > > in CET. As long as the intended offset from UTC is recorded (which it
> is
> > > in a timestamptz) it should be fine.
> >
> > I only really skimmed the entire response but this framing of how
> > timestamptz stores results is wrong.  Once you've stored a timestamptz in
> > PostgreSQL you no longer have any knowledge of the timezone.  If you
> truly
> > need that you need to record that in a different field.  What you do know
> > is that PostgreSQL has a known point-in-time in UTC and can give you back
> > the same value expressed in any other timezone according to the rules in
> > the timezone database.
> >
> > Or, as written verbatim in the documentation:
> > """
> > For timestamp with time zone, the internally stored value is always in
> UTC
> > (Universal Coordinated Time, traditionally known as Greenwich Mean Time,
> > GMT). An input value that has an explicit time zone specified is
> converted
> > to UTC using the appropriate offset for that time zone. If no time zone
> is
> > stated in the input string, then it is assumed to be in the time zone
> > indicated by the system's TimeZone parameter, and is converted to UTC
> using
> > the offset for the timezone zone.
> > """
> >
> > https://www.postgresql.org/docs/12/datatype-datetime.html
> >
> > David J.
>
> You are misinterpreting the documentation, or the
> documentation is incomplete/misleading at that
> location. It doesn't just convert and store the time in
> UTC. It stores the time in UTC and it also stores the
> offset from UTC as determined by the time zone
> specified on input (that section of the documentation
> might not mention that fact but it is true
> nonetheless).
>
> I store localtimes in the database and always see the
> offset when selecting it later. The timezone
> information doesn't just disappear as you (or that
> piece of documentation) might be suggesting.
>
> If you don't believe me, try this:
>
>   create table example (t timestamptz not null);
>   insert into example (t) values (timestamptz '2020-04-16 17:12:33.71768
> Australia/Sydney');
>   select * from  example;
>   drop table example;
>
> When I run that, I see:
>
>   CREATE TABLE
>   INSERT 0 1
> t
>   --
>2020-04-16 17:12:33.71768+10
>   (1 row)
>
>   DROP TABLE
>
> So the timezone is stored (or the offset is stored if
> you prefer). Had it been daylight savings on that date
> in Sydney, the offset would have been "+11". It's all
> fine.
>
> cheers,
> raf
>
>
>

Re: POLL: Adding transaction status to default psql prompt

[Steve Baldwin]

Hi Vik,

I'm not sure why this should be the default when it is easy to override the
default via a psqrc file. If you know enough to do it, you can. Otherwise I
don't think it adds any value as a default since a novice user isn't going
to know what */!/? means. Maybe I'm missing something.

-1 from me.

On Thu, Feb 6, 2020 at 1:55 PM Vik Fearing  wrote:

> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>
> Thanks!
>
> +1 from me.
> --
> Vik Fearing
>
>
>

Re: Determine actual type of a pseudo-type argument

[Steve Baldwin]

I'm not sure what you mean by pseudo-type, but does the pg_typeof()
function help?

Steve

On Mon, Jan 6, 2020 at 9:34 AM Gerald Britton 
wrote:

> If I use a pseudo-type in the argument list of a function definition (SQL
> or plpgsql), how can I determine the actual type used a runtime?
>
> --
> Gerald Britton, MCSE-DP, MVP
> LinkedIn Profile: http://ca.linkedin.com/in/geraldbritton
>

Re: Storing a time interval

[Steve Baldwin]

I agree with Michael. Another consideration is how the composite type is
going to be handled in the DB layer of your processing code (e.g. node,
python, ...).  In the scenario you described it seems unlikely you will be
either having multiple columns of that type on your PO table, or using that
composite type on a different table, so apart from the 'interest' factor,
I'm not seeing any practical benefit.  Composite types are also slightly
painful in the change they bring to the way you reference them. For example
typically you need to surround the outer column in brackets - e.g.
(dates).discount_last_date. If you are using an ORM library, does it know
how to deal with that?

Steve

On Sat, Nov 9, 2019 at 8:11 AM Michael Lewis  wrote:

> CREATE TYPE po_dates AS (
>> po_isssued_datetimestamptz,
>> discount_last_date timestamptz,
>> net_date   timestamptz
>> );
>>
>
> What advantage does combining these three values into a custom composite
> type give you rather than just storing directly? Are you going to reuse
> this po_dates type on many tables?
>

Re: explain plan difference

[Steve Baldwin]

Thanks very much for the explanation Tom !!  You are correct - there are
dropped columns in the original.

Cheers,

Steve

On Mon, Nov 4, 2019 at 3:01 PM Tom Lane  wrote:

> Steve Baldwin  writes:
> > I guess the difference doesn't have a huge bearing (as far as I can tell)
> > on the result, but it just seems odd that the inner-most 'Output' step
> > outputs the entire row in the case of the copy and only the required
> field
> > in the case of the original table. What triggers that behaviour?
>
> The plan with the full output row is actually slightly cheaper, or at
> least so the planner thinks, because it saves a projection step.
> I imagine the reason you're not getting that with the original table
> is that there are some dropped column(s) in the original table, forcing
> the projection to be done to get rid of them.
>
> regards, tom lane
>

explain plan difference

[Steve Baldwin]

Can someone please help me understand this:

b2bc_dev=# *vacuum full analyze invoice;*
VACUUM
Time: 39.671 ms
b2bc_dev=# *create table invoice_copy as select * from invoice;*
SELECT 23
Time: 11.557 ms
b2bc_dev=# *alter table invoice_copy add primary key (id);*
ALTER TABLE
Time: 9.257 ms
b2bc_dev=# *vacuum full analyze invoice_copy;*
VACUUM
Time: 24.369 ms
b2bc_dev=# *explain analyze verbose select max(id::text) from invoice;*
   QUERY PLAN
-
 Aggregate  (cost=4.40..4.41 rows=1 width=32) (actual time=0.852..0.878
rows=1 loops=1)
   Output: max((id)::text)
   ->  Seq Scan on public.invoice  (cost=0.00..4.23 rows=23 width=16)
(actual time=0.016..0.440 rows=23 loops=1)
 Output: id
 Planning time: 0.359 ms
 Execution time: 1.063 ms
(6 rows)

Time: 4.266 ms
b2bc_dev=# *explain analyze verbose select max(id::text) from invoice_copy;*
QUERY PLAN

 Aggregate  (cost=4.40..4.41 rows=1 width=32) (actual time=0.981..1.019
rows=1 loops=1)
   Output: max((id)::text)
   ->  Seq Scan on public.invoice_copy  (cost=0.00..4.23 rows=23 width=16)
(actual time=0.016..0.464 rows=23 loops=1)
 Output: id, client_reference_id, buyer_id, seller_id,
invoice_date, invoice_due_date, invoice_total, po_number, details, status,
statement_id, invoice_reference_number, created, invoice_net,
ar_open_total, bill_date, return_total, api_client_id, client_id,
program_id, billing_payment_term_config_id, preauthorization_id,
tap_synced, updated, shipping_amount, tax_amount, foreign_exchange_fee,
foreign_exchange_fee_rate, return_foreign_exchange_fee,
original_shipping_amount, original_tax_amount, discount_amount,
original_discount_amount, shipping_tax_amount, shipping_discount_amount,
original_shipping_tax_amount, original_shipping_discount_amount,
captured_amount_seller, captured_amount_buyer, adjusted_amount, ar_closed_on
 Planning time: 0.441 ms
 Execution time: 1.254 ms
(6 rows)
b2bc_dev=# *select version();*
version
---
 PostgreSQL 10.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0)
6.4.0, 64-bit
(1 row)
Time: 4.916 ms

I guess the difference doesn't have a huge bearing (as far as I can tell)
on the result, but it just seems odd that the inner-most 'Output' step
outputs the entire row in the case of the copy and only the required field
in the case of the original table. What triggers that behaviour?

Thanks,

Steve

Re: regr_slope returning NULL

[Steve Baldwin]

Thanks Dean, that's really helpful. Because my x axis values are actually
derived from 'extract(epoch from tstz_col)', it is simple for me to
subtract an offset.

Cheers,

Steve

On Sun, Mar 24, 2019 at 7:55 PM Dean Rasheed 
wrote:

> On Sun, 24 Mar 2019 at 08:01, Steve Baldwin 
> wrote:
> >
> > Thanks Tom,
> >
> > I've tried this on 11.2 (OS X 10.14.3, installed locally) and 10.6 (AWS
> RDS) instances with identical results.  The values you show are identical
> to those returned by Oracle so that's great but why am I seeing different
> results?
> >
>
> This is caused by the large magnitude of the ts values, which causes a
> cancellation error in the Sxx calculation, which is what commit
> e954a727f0 fixed in HEAD, and will be available in PG12 [1].
>
> You can see that by including regr_sxx in the results. With PG11, this
> gives the following:
>
> select id, regr_slope(elapsed, ts) as trend, regr_sxx(elapsed, ts) as sxx
>   from sb1 group by id;
>
>   id  |trend | sxx
> --+--+-
>  c742 |  |   0
>  317e |  |   0
>  5fe6 | 5.78750952760444e-06 | 19905896448
>  3441 |  |   0
> (4 rows)
>
> Those zeros for Sxx are the result of calculating the sum of the
> squares of ts values and then subtracting off the square of the mean,
> which results in a complete loss of accuracy because the intermediate
> values are so large they don't differ according to double precision
> arithmetic.
>
> A workaround in PG11 is to just offset the ts values by something
> close to their mean (offsetting the ts values by a constant amount
> shouldn't affect the mathematical result, but does eliminate the
> cancellation errors):
>
> select id, regr_slope(elapsed, ts-1552892914) as trend,
>regr_sxx(elapsed, ts-1552892914) as sxx
>   from sb1 group by id;
>
>   id  |trend |sxx
> --+--+
>  c742 | 19.6077357654714 | 0.0468182563781738
>  317e |-1.08385104429772 |   59.2381523980035
>  5fe6 | 5.78750948360697e-06 |   19905896596.7403
>  3441 |-3.82839508895523 |   20.1098628044128
> (4 rows)
>
>
> For PG12 the algorithm for calculating these quantities has been
> changed by e954a727f0, so the result should be more accurate
> regardless of the offset:
>
> select id, regr_slope(elapsed, ts) as trend, regr_sxx(elapsed, ts) as sxx
>   from sb1 group by id;
>
>   id  |trend |sxx
> --+--+
>  c742 | 19.6078587812905 | 0.0468179252929986
>  317e | -1.0838511987809 |   59.2381423694815
>  5fe6 | 5.78750948358674e-06 |   19905896596.7605
>  3441 |-3.82839546309736 |   20.1098619909822
> (4 rows)
>
> select id, regr_slope(elapsed, ts-1552892914) as trend,
>regr_sxx(elapsed, ts-1552892914) as sxx
>   from sb1 group by id;
>
>   id  |trend |sxx
> --+--+
>  c742 | 19.6078431374563 | 0.046817990382
>  317e |-1.08385109620679 |   59.2381495556381
>  5fe6 | 5.78750948360693e-06 |   19905896596.7403
>  3441 |-3.82839509931361 |20.109862749992
> (4 rows)
>
> Regards,
> Dean
>
> [1] https://github.com/postgres/postgres/commit/e954a727f0
>

Re: regr_slope returning NULL

[Steve Baldwin]

Thanks Tom,

I've tried this on 11.2 (OS X 10.14.3, installed locally) and 10.6 (AWS
RDS) instances with identical results.  The values you show are identical
to those returned by Oracle so that's great but why am I seeing different
results?

This is from my local install:

log=# select version();
 version

--
 PostgreSQL 11.2 on x86_64-apple-darwin18.2.0, compiled by Apple LLVM
version 10.0.0 (clang-1000.11.45.5), 64-bit

Cheers,

Steve

On Sun, Mar 24, 2019 at 4:34 PM Tom Lane  wrote:

> Steve Baldwin  writes:
> > Consider the following:
> > ...
> > log=# select id, regr_slope(elapsed, ts) as trend from sb1 group by id;
> >   id  |trend
> > --+--
> >  c742 |
> >  317e |
> >  5fe6 | 5.78750952760444e-06
> >  3441 |
> > (4 rows)
>
> Hm, I get
>
> regression=# select id, regr_slope(elapsed, ts) as trend from sb1 group by
> id;
>   id  | trend
> --+---
>  c742 |19.607858781290517
>  317e |   -1.0838511987808963
>  5fe6 | 5.787509483586743e-06
>  3441 |-3.828395463097356
> (4 rows)
>
> What platform are you doing this on, and what exactly is the PG version?
>
> > If pg is correctly returning NULL, I'd be interested to understand the
> > circumstances under which this can occur.
>
> The source code shows two cases in which NULL would be returned:
>
> /* if N is 0 we should return NULL */
> if (N < 1.0)
> PG_RETURN_NULL();
>
> /* per spec, return NULL for a vertical line */
> if (Sxx == 0)
> PG_RETURN_NULL();
>
> Maybe the cases you're looking at are sufficiently numerically
> ill-conditioned that you could get Sxx == 0 depending on platform-
> specific roundoff error, but it seems fishy.
>
> regards, tom lane
>

regr_slope returning NULL

[Steve Baldwin]

Hi,

I'm not sure whether or not this is a bug, so I've posted here first (after
having posted on Stack Overflow).

Consider the following:

log=# create table sb1(id text, elapsed int, ts numeric);
CREATE TABLE
log=# insert into sb1 values
('317e',86,1552861322.627),('317e',58,1552861324.747),('317e',52,1552861325.722),('317e',58,1552861326.647),('317e',82,1552861327.609),('317e',118,1552861328.514),('317e',58,1552861329.336),('317e',58,1552861330.317),('317e',54,1552861330.935),('3441',68,1552861324.765),('3441',84,1552861326.665),('3441',56,1552861327.627),('3441',50,1552861330.952),('5fe6',42,1552993248.398),('5fe6',44,1552993255.883),('5fe6',44,1553166049.261),('c742',62,1552861322.149),('c742',68,1552861322.455);
INSERT 0 18
log=# select * from sb1 order by id, ts;
  id  | elapsed |   ts
--+-+
 317e |  86 | 1552861322.627
 317e |  58 | 1552861324.747
 317e |  52 | 1552861325.722
 317e |  58 | 1552861326.647
 317e |  82 | 1552861327.609
 317e | 118 | 1552861328.514
 317e |  58 | 1552861329.336
 317e |  58 | 1552861330.317
 317e |  54 | 1552861330.935
 3441 |  68 | 1552861324.765
 3441 |  84 | 1552861326.665
 3441 |  56 | 1552861327.627
 3441 |  50 | 1552861330.952
 5fe6 |  42 | 1552993248.398
 5fe6 |  44 | 1552993255.883
 5fe6 |  44 | 1553166049.261
 c742 |  62 | 1552861322.149
 c742 |  68 | 1552861322.455
(18 rows)

log=# select id, regr_slope(elapsed, ts) as trend from sb1 group by id;
  id  |trend
--+--
 c742 |
 317e |
 5fe6 | 5.78750952760444e-06
 3441 |
(4 rows)

Interestingly, the same dataset and function in Oracle 11.2 returns the
same value for one of the ids and non-null values for the other ids :

SQL> select * from sb1 order by id, ts;

IDELAPSED   TS
-- -- 
317e   86   1552861322.627
317e   58   1552861324.747
317e   52   1552861325.722
317e   58   1552861326.647
317e   82   1552861327.609
317e  118   1552861328.514
317e   58   1552861329.336
317e   58   1552861330.317
317e   54   1552861330.935
3441   68   1552861324.765
3441   84   1552861326.665
3441   56   1552861327.627
3441   50   1552861330.952
5fe6   42   1552993248.398
5fe6   44   1552993255.883
5fe6   44   1553166049.261
c742   62   1552861322.149
c742   68   1552861322.455

18 rows selected.

SQL> select id, regr_slope(elapsed, ts) from sb1 group by id;

ID REGR_SLOPE(ELAPSED,TS)
-- --
c742   19.6078431
5fe6   5.7875E-06
317e   -1.0838511
3441   -3.8283951

If pg is correctly returning NULL, I'd be interested to understand the
circumstances under which this can occur.

Thanks,

Steve