Check this out
https://www.postgresql.org/docs/16/arrays.html#ARRAYS-INPUT
You can use
('{' || email || '}')::varchar(64)[]
or the syntax I suggested earlier.
On Wed, Jul 3, 2024 at 4:13 PM Rich Shepard
wrote:
> On Wed, 3 Jul 2024, David G. Johnston wrote:
>
> > Yeah, the simply cast
The USING phrase basically answers the question how do I convert an
existing value of the old type to the new type.
On Wed, Jul 3, 2024 at 4:03 PM Rich Shepard
wrote:
> On Wed, 3 Jul 2024, Rich Shepard wrote:
>
> > What I've tried:
> > bustrac=# alter table people alter column email set data
You could try
ALTER TABLE ... SET TYPE TEXT[] USING ARRAY[email]::TEXT[]
something along these lines.
On Wed, Jul 3, 2024 at 3:58 PM Rich Shepard
wrote:
> On Tue, 2 Jul 2024, Christophe Pettus wrote:
>
> > To be clear, I wasn't suggesting stuffing them all into a text column
> with
> > a
On Mon, Jun 10, 2024 at 8:50 PM Rich Shepard
wrote:
> My question is whether I can create new rows for all three tables in the
> same sql source file. Since the location and contact tables require
> sequence
> numbers from the company and location tables is there a way to specify,
> e.g.,
Hi,
This is a VACUUM FREEZE process.
-[ RECORD 1 ]--+--
pid| 129471
datid | 16401
datname| feed
relid | 1889166
phase | scanning heap
heap_blks_total| 1254901
heap_blks_scanned | 1017524
heap_blks_vacuumed |
On Sun, May 26, 2024 at 8:46 PM sud wrote:
> Would you agree that we should have two standby, one with default
> max_standby_streaming_delay (say 10 sec ) which will be mainly used as high
> availability and thus will be having minimal lag. and another standby with
> max_standby_streaming_delay
On Sun, May 26, 2024 at 11:16 AM sud wrote:
> On Sun, May 26, 2024 at 1:43 PM Torsten Förtsch
> wrote:
>
>> On Sat, May 25, 2024 at 11:00 PM sud wrote:
>>
>>>
>>> But i have one question here , does max_standby_streaming_delay = 14 ,
>>> mea
On Sat, May 25, 2024 at 11:00 PM sud wrote:
>
> But i have one question here , does max_standby_streaming_delay = 14 ,
> means the queries on the standby will get cancelled after 14 seconds?
>
No, your query gets cancelled when it stalls replication for >14 sec. If
your master is idle and does
Please suggest here.
> Data size is 3TB
>
> On Thu, May 23, 2024, 3:53 PM Torsten Förtsch
> wrote:
>
>> As the error message says, your query was aborted due to it conflicting
>> with recovery. There are many ways to deal with that. You could enable
>> hot_standby_fe
As the error message says, your query was aborted due to it conflicting
with recovery. There are many ways to deal with that. You could enable
hot_standby_feedback on the replica. You could disconnect the replica from
the master for the time the COPY takes (reset primary_conninfo). You could
Sorry, to correct myself. The pg_xact bit is written with the next
checkpoint. But the COMMIT record in the WAL is there.
On Thu, May 9, 2024 at 5:14 PM Torsten Förtsch
wrote:
> I would not find this behavior surprising in particular if you have a
> synchronous replica. Acc
I would not find this behavior surprising in particular if you have a
synchronous replica. According to the documentation of synchronous_commit:
The local behavior of all non-off modes is to wait for local flush of
WAL to disk.
This is when the logical decoder sees the item. But that does not
As part of the commit operation, Postgres inserts the notification into a
queue. Naturally, that insert is guarded by a lock and that lock is
released only at the very end of the commit operation. This effect gets
much worse if you also configure synchronous replication because commit
finishes
Hi,
imagine a simple table with 1 row
=# table tf;
i | x
---+
1 | xx
(1 row)
And this query:
with x as (update tf set i=i+1 returning *)
, y as (update tf set x=x||'yy' returning *)
select * from x,y;
My PG14 gives this result
i | x | i | x
---+---+---+---
(0 rows)
To me that was a
On Thu, Nov 23, 2023 at 2:29 PM Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> wrote:
> smrdbprod=# select crart_id, chemin, count(*) from smrr_mgr.formula group
> by crart_id, chemin having count(*) > 1;
> crart_id | chemin | count
> -++---
>
Hi,
This is part of a query plan:
Nested Loop Left Join (cost=26.32..47078866.36 rows=1344945195 width=626)
-> Nested Loop Left Join (cost=25.74..5312.48 rows=1344945195
width=608)
-> Nested Loop Left Join (cost=6.79..2876.77 rows=102 width=373)
-> Nested Loop
Hi,
I have an old, several TB table. That table has a child table (table
inheritance) which is much smaller. Each row represents a certain process.
The original idea was while the process is in progress it is stored in the
small table. Once it has reached its final state, it is moved to the big
Hi,
I wanted to create an exclusion constraint like
EXCLUDE (c1 WITH =, c2 with <>)
This gives an error:
operator <>(integer,integer) is not a member of operator family
"integer_ops"
I can resolve that by using gist and the btree_gist extension.
My question is can I somehow express something
On Tue, Feb 7, 2023 at 12:47 PM Matthias Apitz wrote:
>
>
> We saw the following message in the file postgres-serverlog.error:
>
> 2023-01-24 17:16:16.578 CET [17468] ERROR: deadlock detected
> 2023-01-24 17:16:16.578 CET [17468] DETAIL: Process 17468 waits for
> ShareLock on transaction
Is there a way to prevent this from happening? I know I can use the PK
constraint name or rename the OUT variable i. The question is can this be
resolved while keeping the arbiter inference and the variable name.
CREATE TABLE x.x (
i INT PRIMARY KEY
);
CREATE OR REPLACE FUNCTION x.ins(p_i
Hi,
not sure if this is known behavior.
Server version is 14.6 (Debian 14.6-1.pgdg110+1).
In a PITR setup I have these settings:
recovery_target_xid = '852381'
recovery_target_inclusive = 'false'
In the log file I see this message:
LOG: recovery stopping before commit of transaction 852381,
On Mon, Jan 9, 2023 at 10:59 AM Adrien Nayrat
wrote:
>
> * We can't perform PITR on a cluster without any activity since 13
> * It seems creating restore point doesn't record a timestamp in wal.
>
I have a cron job that runs this every 5 minutes:
SELECT txid_current() WHERE
You can set up wal archiving and set restore_command before you start the
replica. Then you can use pg_basebackup with `-Xnone --no-slot`.
Alternatively or in combination, use a quicker backup as Laurenz explained.
On Mon, Nov 28, 2022 at 11:17 AM Srinivasa T N wrote:
> Hi All,
>I am using
This is what happens:
WITH elev_Tlse_Blagnac AS (
SELECT elev FROM weather_stations WHERE id=31069001
)
SELECT w.id, w.name, w.elev
FROM weather_stations AS w
WHERE elev > (SELECT x.elev
FROM elev_Tlse_Blagnac AS x
WHERE w.id BETWEEN 3100 and 3199);
Hi,
I have a few databases with several TB-sized tables. We recently moved one
of those databases from AWS EC2 to GCP. Today I ran VACUUM FREEZE on those
tables and every time I saw our application freezing up (and throwing tons
of errors) for a few minutes right after the message about the end
t; sum on regular column should not be greater than 120 per person.
>
> sum of regular and overtime columns must be same as sum of hours column
> in hours table for every person.
>
> Andrus.
> 13.02.2022 14:46 Torsten Förtsch kirjutas:
>
> something like
>
> SELE
something like
SELECT *
, least(sum(hours) OVER w, 120) AS regular
, greatest(sum(hours) OVER w - 120, 0) AS overtime
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id);
job_id | person | hours | regular | overtime
++---+-+--
2 |
On Wed, Jan 12, 2022 at 1:22 PM Issa Gorissen wrote:
> So I have this question, how to script the making of base backup for
> transfer on the slave server when the two SQL functions must be called
> in the same connection, in Bash for example; is this doable?
>
Not sure if I understand the
On Thu, Jun 17, 2021 at 1:04 PM Sakshi Jain
wrote:
How to listen from within a psql process and get the payloads?
>
> Do Postgresql have any such a mechanism where in a session a process send
> a "listen " sql command and then gets a message if someone in the
> other session issued a "notify ".
Have you tried session_user?
create function xx() returns table (cur text, sess text)
security definer language sql as $$
select current_user::text, session_user::text;
$$;
Then log in as different user and:
=> select (xx()).*;
cur| sess
--+---
postgres | write
On Tue,
Hi,
out of curiosity I created the following setup, all with 9.6 and pglogical.
D1 is configured as provider with a replication set that contains only 1
table. Only inserts are replicated.
D2 is configured as subscriber for that replication set. Replication works,
all inserts on D2 arrive also
Broken index?
I had a similar problem a while ago. We were streaming a DB from a Debian
box to an alpine docker image. The underlying system libraries were a
little different and that resulted in broken index behavior.
On Wed, Apr 3, 2019 at 3:06 PM Felix Ableitner wrote:
> Hello,
>
> I'm
On Mon, Dec 17, 2018 at 2:07 PM wrote:
> many thanks -- too bad I am still using 9.3
>
>
not sure if it has been mentioned in the thread so far. But according to
the docs, 9.3 psql does support the \gset command. So you should be able to
do something like this:
select case when exists (select 1
Hi,
I am working on restoring a database from a base backup + WAL. With the
default settings the database replays about 3-4 WAL files per second. The
startup process takes about 65% of a CPU and writes data with something
between 50 and 100 MB/sec.
Is there a way to speed that up? The disk can
12:20:26 +0200, Torsten Förtsch wrote:
> > if I understand it correctly, backend_xmin in pg_stat_replication is the
> > xmin that's reported back by hot_standby_feedback. Given there are no
> > long-running transactions on the replica, I presume that value should be
> > pret
Hi,
I have a table with a really small number of rows, usually about 1500,
sometimes may be up to 5000. The usage pattern of that table is such that
rows are inserted and kept for a while, mostly seconds or minutes but
theoretically up to 1 year. After that they are deleted. No updates, just
Hi,
is there a way to find if a certain page in a data file is referenced by a
btree index?
I found a few completely empty pages in one of my tables. I am not sure if
that's corruption or just bloat. Now I am thinking I could use an index,
the PK for instance, and see if it references these
On Thu, Jul 19, 2018 at 8:10 PM Tom Lane wrote:
> =?UTF-8?Q?Torsten_F=C3=B6rtsch?= writes:
> > I know that. My question was about the execution order of f1 and f2 in
> > "SELECT f1(), f2()". In theory they can be executed in any order. But
> since
> > the side effect in nextval determines the
On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver
wrote:
> On 07/19/2018 07:15 AM, Torsten Förtsch wrote:
> > Hi,
> >
> > assuming
> >
> > SELECT nextval('s'), currval('s');
> >
> > or
> >
> > SELECT * FROM (VALUES (nextval('s'), currval('
Hi,
assuming
SELECT nextval('s'), currval('s');
or
SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
is there any guarantee that the 2 output values are the same?
Thanks,
Torsten
Hi,
I am absolutely sure a certain value of one of my ENUM types is not used in
the entire database. Now I am asked to drop that value. Unfortunately,
there is no ALTER TYPE DROP VALUE.
On my development box I tried
delete from pg_enum
where enumtypid='my_type_name'::regtype
and
On Wed, May 16, 2018 at 8:14 AM, Philipp Kraus <
philipp.kr...@tu-clausthal.de> wrote:
> Hello,
>
> I have got a function with a reg expr to split chemical formulas e.g. H2O
> -> H2 O.
>
> CREATE OR REPLACE FUNCTION daimon.text2sumformula(text) RETURNS text[] AS
> $$
> select
Hi,
according to the documentation, dblink_send_query sends the query to be
executed asynchronously. I tried this out in the following function and it
works as expected:
CREATE OR REPLACE FUNCTION t_par () RETURNS TABLE (
tx_time TIMESTAMP,
end_time TIMESTAMP
) AS $def$
DECLARE
v_q
43 matches
Mail list logo
