,
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
> > --+--
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
>
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
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=#
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
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)
> >
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
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?
>
> --
>
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
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
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
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
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 use
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
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
,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
> |
What am I missing?
b2bcreditonline=# select * from foo;
id | js
|f1 | f2
++---+
1 | [{"key": "r1kval", "key2": "r1k2val"}, {"key":
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
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
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
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
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
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
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
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
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 (unl
ion
-
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
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
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 doc
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 someth
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
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
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)
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
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:
>
>> T
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
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
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 <
nisms 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 adv
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
f: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: u
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
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,
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
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 execu
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
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
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"
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,
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
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
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
| 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. M
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,
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
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'.
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
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
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 ]+-
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 |
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
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
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
63 matches
Mail list logo
